SQL Server NULL Integer to Empty String using ISNULL

Question

My curiosity always gets the best of me and I've searched online for an explanation to this and came up with nothing (could be because I didn't use the right terms.)

Can someone please explain why SQL Server returns a value of zero (0) when the following is executed, instead of an empty string ('').

    DECLARE @I AS INT
    SET @I = NULL
    SELECT ISNULL(@I, '') -- 0

SQL Server is implicitly casting '' to an integer, which is 0 (zero). — Andrew, Aug 19 '14 at 21:26

hunch_hunch · Accepted Answer · 2014-08-19T21:43:15.950

4

As declared here, the second argument to ISNULL is the replacement_value, which "must be of a type that is implicitly convertible to the type of check_expresssion." Implicitly converting '' to INT results in 0.

edited Aug 19 '14 at 21:43

answered Aug 19 '14 at 21:27

hunch_hunch

2,283
1
21
26

1

Uh, the value '' IS implicitly convertible. It casts as 0. select cast('' as INT) -- returns 0 – Kyle Hale Aug 19 '14 at 21:29
1

Thanks.. Looks like the answer was right under my nose. – Ahz Aug 19 '14 at 21:45

score 2 · Answer 2 · answered Aug 19 '14 at 21:25

2

Because @I is declared as an INT, the empty string is implicitly CAST as an integer resulting in a ZERO.

answered Aug 19 '14 at 21:25

T McKeown

12,971
1
25
32

1

have the same issue. Hope somebody will propose better solution then changing source column data type. Read here: http://stackoverflow.com/questions/39937037/convert-int-column-values-to-an-empty-string-using-isnull – Data Engineer Oct 08 '16 at 20:31

score -2 · Answer 3 · answered Aug 02 '18 at 14:25

I know is an old question, but I want to share. The issue here is not because the casting of the ' ' expression, is because the int data type is not null-able and, when you try to SET @I = NULL SQL set the default value zero(0). That's why the statement ISNULL(@I, '') does not find any null in @I and returns its value(0).

Hope it helps somebody out there.

SQL Server NULL Integer to Empty String using ISNULL

3 Answers3