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

Question

First of all, I would like to know how CAST works with NULL fields and how it behaves when NULL ?

For example, in the expression:

 (CAST(INT_FIELD as nvarchar(100)) 

What happens if INT_FIELD is NULL ?

The reason is that when I try to do the following:

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

I get NULL , although SOME_OTHER_FIELD not null. I assume it has some kind of logic, but 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 operations on a line between NULL will result in NULL . CAST from 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 this idea.

Answer 2

Take a look at COALESCE, where you can find the first nonzero value and return 0 if all are zero, for example:

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

Answer 3

Try using COALESCE

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

Answer 4

Usually 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 )