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
– Andrew, Commented Aug 19, 2014 at 21:26

hunch_hunch · Accepted Answer · 2014-08-19 21:43:15Z

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, 2014 at 21:43

answered Aug 19, 2014 at 21:27

hunch_hunch

2,3411 gold badge23 silver badges27 bronze badges

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

2 Comments

Kyle Hale Over a year ago

Uh, the value '' IS implicitly convertible. It casts as 0. select cast('' as INT) -- returns 0

Ahz Over a year ago

Thanks.. Looks like the answer was right under my nose.

T McKeown · Accepted Answer · 2014-08-19 21:25:52Z

2

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

answered Aug 19, 2014 at 21:25

T McKeown

12.9k1 gold badge27 silver badges34 bronze badges

1 Comment

Data Engineer Over a year ago

have the same issue. Hope somebody will propose better solution then changing source column data type. Read here: stackoverflow.com/questions/39937037/…

Pedro Lebron · Accepted Answer · 2018-08-02 14:25:57Z

-2

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.

answered Aug 2, 2018 at 14:25

Pedro Lebron

2813 silver badges3 bronze badges

Collectives™ on Stack Overflow

SQL Server NULL Integer to Empty String using ISNULL

3 Answers 3

2 Comments

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Linked

Related