Sql-server – SQL Server Replace not returning expected result

sql server

Shouldn't the below code return 0?

SELECT REPLACE(ISNULL('',0),'',0)

This doesn't return 0 either….

SELECT REPLACE('','',0)

I have an nvarchar field in a table that contains numerical values but sometimes is blank (not null).

I have a query that checks if that field is < a number in another field but I get

"Error converting data type nvarchar to numeric."

errors if the field is blank. I am trying to convert the blank values ('') to zeroes during query execution so that I don't get the conversion error but I just realized that

SELECT REPLACE('','',0)

will not convert the blank field to a 0.

I tried using the below code to try and avoid the conversion error but it is not evaluating to 0 when the threshold field is blank as I expected:

SELECT CAST(REPLACE(ISNULL(Threshold,0),'',0) as decimal(4,2))

How can I get the code to evaluate to 0 when the threshold field is blank.

I'm guessing I could write an if or case statement that skips the blank ('') value threshold but I am thinking there has to be a way to do it all inline, something like what I am trying to do with the above code.

Best Answer

ISNULL('','0')

Returns the first argument '' (as that is not null) so your question boils down to asking why

SELECT REPLACE('','','0')

Doesn't return 0.

The documentation for REPLACE states

string_pattern Is the substring to be found. string_pattern can be of a character or binary data type. string_pattern cannot be an empty string (''), and must not exceed the maximum number of bytes that fits on a page.

TSQL is not unusual in this respect.

In C# attempting "Some String".Replace("", "0") would give an error "String cannot be of zero length."

This is not surprising as in any string of text you could argue that there are infinitely many empty strings.

There could be 20 between the "T" and "h" at the start of this sentence. As they are zero length how could we tell?

For your actual use case you can use

SELECT CASE
         WHEN Threshold <> ''
           THEN CAST(Threshold AS DECIMAL(4, 2)) --Not null and not empty string
         ELSE 0
       END

Related Solutions

Sql-server – Getting “Conversion failed when converting the nvarchar value to data type int.”

Write your query this way. It includes a better test for integers and dumps ISNUMERIC that returns 1 for '-.', for example.

SELECT
    zip,
    coordinates
FROM (
    SELECT
        CASE WHEN ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%' THEN
            CONVERT(int, [ZIP5]) END zip,
        CONVERT(varchar(max), geom) AS coordinates
    FROM 
        [SpatialData].[dbo].[zip5]
    WHERE
        ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%'
) AS t1
WHERE
    zip >= 85000 AND
    zip < 86000

See this Connect item

SQL Server is free to evaluate the WHERE/SELECT clause in the order that it decides is optimized. A view or derived table is not materialized can easily be expanded into, from the outer query.

What SQL Server is compiling is really a query that looks like this

SELECT
    CONVERT(int, [ZIP5]) AS zip,
    CONVERT(varchar(max), geom) AS coordinates
FROM 
    [SpatialData].[dbo].[zip5]
WHERE
    ISNUMERIC([ZIP5]) = 1
    AND CONVERT(int, [ZIP5]) >= 85000
    AND CONVERT(int, [ZIP5]) < 86000

You can inspect the query plan of your original, but my guess from looking at the structure is that the WHERE clause uses the expression CONVERT(int, [ZIP5]) twice, so it makes sense to streamline the resolution (calculation to a result) of the expression in the process of retrieving data from the table. This puts the processing of the SELECT clause before the WHERE, so your ISNUMERIC() = 1 never got a chance to filter the bad eggs.

It is by-design.

Sql-server – Migrating from SQL Server to MySQL using MySQL Workbench tool

Well, since you have data currently stored in SQL Server, and it's already in an NVARCHAR column, then either it's an NVARCHAR <= 4000 (in which case you can't lose any data, and should just change all instances of NVARCHAR(8000) to NVARCHAR(4000)), or it's an NVARCHAR(MAX) column, in which case you change all instances of NVARCHAR(8000) to NVARCHAR(MAX). Or just leave out those CASTs - do you really need them?

As an aside, you should probably change as NTEXT to as NVARCHAR(MAX) as well.

Best Answer

Related Solutions

Sql-server – Getting “Conversion failed when converting the nvarchar value to data type int.”

Sql-server – Migrating from SQL Server to MySQL using MySQL Workbench tool

Related Question