2

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.

4
  • Put the 0 like '0'
    – Biju jose
    Jun 18, 2016 at 20:24
  • @Bijujose SELECT REPLACE('','','0') still returns blank instead of 0
    – Juan Velez
    Jun 18, 2016 at 20:28
  • 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
    – Biju jose
    Jun 18, 2016 at 20:33
  • No white space in cell. Apparently Replace can not search for a empty string pattern (per answer below).
    – Juan Velez
    Jun 20, 2016 at 13:56

1 Answer 1

5
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.