CODEWITHSUNDEEP
sql-servert-sqlcasecoalesceisnull
j3rbrown
j3rbrown

Reputation: 163

CASE Statement with ISNULL(0,'')

I would expect the following SQL statement to return b.

Can you point out what I am doing wrong?

SELECT CASE WHEN ISNULL(0,'')='' THEN 'a' ELSE 'b' END

Upvotes: 2

Views: 3507

Answers (2)

Allan S. Hansen
Allan S. Hansen

Reputation: 4081

It comes from the the casting of the data types. You compare an integer to a character

If you run 

SELECT CASE WHEN ISNULL(CAST(0 AS VARCHAR(255)),'') = '' THEN 'a' ELSE 'b' END

you will get b
Otherwise '' will be casted to an integer which is 0 thus making the comparison 0 = 0 which is true and therefore returns 'a'.

Upvotes: 6

SMS
SMS

Reputation: 462

SELECT ISNULL(0, '')

Above query returns value 0, so the result is 'a'

Change the query to below, and you will get 'b'

SELECT CASE WHEN ISNULL('0','')='' THEN 'a' ELSE 'b' END

Upvotes: 0

Related Questions