CODEWITHSUNDEEP
sqlsql-servernull
TehBoyan
TehBoyan

Reputation: 6890

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?

Upvotes: 29

Views: 117703

Answers (4)

Lamak
Lamak

Reputation: 70638

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.

Upvotes: 43

Vinay
Vinay

Reputation: 1064

Try using COALESCE

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

Upvotes: 4

downforce
downforce

Reputation: 51

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;

Upvotes: 5

a1ex07
a1ex07

Reputation: 37364

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)

Upvotes: 3

Related Questions