Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Casting a VARCHAR to INT returns zero if null?

Author  Topic 

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-12-13 : 10:27:46
Hope someone can help, I don't know if this possible.

I have a UNION query. The first half selects several fields including a field called NCAT which is of type VARCHAR. If that field is empty, the output for that field is empty.

So far so good.

When I union this, I am including the line

CAST(NCAT AS INT)+ 210 AS NCAT


which adds 210 to another field if certain criteria are met.

This has the unwanted effect of returning a zero for all of the fields that were previously empty.

I want them to remain empty - is there a way I can achieve this?

Thanks in advance

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-13 : 10:45:52
What is empty? I'm guessing blank rather than null.

try coalesce(convert(varchar(20),cast(nullif(NCAT,'') as int)+210),'') as NCAT
the convert, coalesce is to put it back to blank rather than null. If you are happy with null and want a numeric then leave it out.
Note - you can't return an numeric blank - you need to convert to character for that.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-12-13 : 11:27:34
Outstanding, thank you very much
Go to Top of Page
   

- Advertisement -