SQL Server ISNULL function evaluating empty string as Zero(0)

Question

Can anybody explain why the following query is evaluating the to 'null' even though the value assigned to the @Test = NULL and it is checked against 0 in CASE?

declare @Test varchar = NULL
    SELECT (CASE ISNULL(@Test, '')
            WHEN 0 THEN 'null'
            ELSE 'not null'
        END) as 'value'

this returns 'null'

AND

declare @Test varchar = NULL
    SELECT (CASE ISNULL(@Test, '')
            WHEN '' THEN 'null'
            ELSE 'not null'
        END) as 'value'

this also returns 'null'

so evaluating ISNULL(NULL,'') to '' or 0 does not make any difference?

You are implicitly casting @Test to an INT before comparing with 0 - the equivalent of WHEN CAST(@Test AS INT) = 0. — to StackOverflow
– to StackOverflow, Commented Dec 19, 2017 at 9:27
To compare '' against 0 the server has to cast it to int first, ie to 0. What are you trying to do with that code? Why not use the appropriate IS NULL, ie CASE WHEN @Test IS NULL then ... ELSE .. END? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 19, 2017 at 9:27
@PanagiotisKanavos thanks for the explaination Actually I was going thru some older moules in a project and I found this code like in the 1st condition of the question. and I was like why is it not evaluating the condition as false while evaluating '' to 0 — Rajan Patel
– Rajan Patel, Commented Dec 19, 2017 at 9:34
@RajanPatel I hope this is the smallest snake you'll find in the module. Unfortunately, such snakes have company — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 19, 2017 at 9:39

Dale K · Accepted Answer · 2022-04-05 02:38:02Z

1

Better way to try it out as below

CASE WHEN  @Test IS NULL THEN 0  -- Type numeric
     ELSE '-'                 -- Type VARCHAR
     END AS outputvlaue

this will give you an error as you cannot have two return type out of a case expression, so try as below

CASE WHEN  @Test IS NULL THEN 0  -- Type numeric
     ELSE 123                 -- Type numeric
     END AS outputvlaue

above will work

output of query is correct ISNULL(@Test, '') means return '' value when there is null.

Both query works because case..when expression when you try like this

  SELECT (CASE ISNULL(@Test, '')
            WHEN 0 THEN 'null'
            ELSE 'not null'
        END) as 'value'

Isnull returns you true value , as expression you given in case expression evaluated as true which is ISNULL(@Test, '') and its true ..

so you have to try like this

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

will return you 'a'

edited Apr 5, 2022 at 2:38

Dale K

28.2k15 gold badges59 silver badges85 bronze badges

answered Dec 19, 2017 at 9:24

Pranay Rana

177k37 gold badges244 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rajan Patel Over a year ago

output of 2nd query seems correct. but why it is not returning 'not null' as ISNULL(NULL,'') will return '' and we are checking it against 0.

Panagiotis Kanavos Over a year ago

@RajanPatel your code asks for '' to be converted to 0 before checking. Why aren't you using IS NULL ? Why the convertion to '' and then to int?

Pranay Rana Over a year ago

@RajanPatel - because in first case it comparing with numeric vlaue thats why it get worked ..you can tried first case like this CASE ISNULL(@Test, 1) WHEN 0 THEN 'null' ELSE 'not null'

Collectives™ on Stack Overflow

SQL Server ISNULL function evaluating empty string as Zero(0)

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related