How to control casting of null int field to varchar in sql server?

Question

First of all I would like to know how does CAST work with NULL fields and how does it behave when the value is NULL?

For example in the expression:

(CAST(INT_FIELD as nvarchar(100)) 

what happens if the value INT_FIELD is NULL?

The reason is that when I'm trying to do the following:

SELECT (CAST(INT_FIELD as nvarchar(100)) + ' ' + SOME_OTHER_FIELD FROM SOME_TABLE; 

I'm getting NULL even though the SOME_OTHER_FIELD is not null. I'm guessing it has some kind of logic that NULL + something = NULL but I'm not sure.

How can I control this behavior?

Answer 1

You need to use ISNULL or COALESCE, since most row operation between a NULL is gonna result in NULL. CAST of a NULL returns NULL and NULL + something is also NULL. In your example you should do something like this:

SELECT ISNULL(CAST(INT_FIELD as nvarchar(100)),'') + ' ' + ISNULL(SOME_OTHER_FIELD,'') FROM SOME_TABLE; 

Of course, in my example, if both fields are NULL it will return ' ' instead of '', but you get the idea.

Answer 2

Look into COALESCE, where you can find the first non-null and return 0 if all are null, e.g:

SELECT (CAST(COALESCE(INT_FIELD,0) as nvarchar(100)) + ' ' + SOME_OTHER_FIELD FROM SOME_TABLE;