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

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?

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.

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;

Try using COALESCE

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

Normally, NULL +(-,/,*, etc) something = NULL. You can use

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

or you can SET CONCAT_NULL_YIELDS_NULL OFF (more details)