SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Casting a VARCHAR to INT returns zero if null?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rasta Pickles
Posting Yak Master

United Kingdom
169 Posts

Posted - 12/13/2012 :  10:27:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3371 Posts

Posted - 12/13/2012 :  10:45:52  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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

United Kingdom
169 Posts

Posted - 12/13/2012 :  11:27:34  Show Profile  Reply with Quote
Outstanding, thank you very much
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000