SQL Server Replace not returning expected result

Question

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.

• Biju jose: Put the 0 like '0'
• Juan Velez: @Bijujose SELECT REPLACE('','','0') still returns blank instead of 0
• Biju jose: Any white spaces in the cell? If there put an ltrim rtim to remove the white space or put a space inside the replacement expression
• Juan Velez: No white space in cell. Apparently Replace can not search for a empty string pattern (per answer below).

Answer 1

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