Case When with NULL - SQL Server Forums

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

SQL Server 2012 Forums

Transact-SQL (2012)

Case When with NULL

Author

Topic

rama108
Posting Yak Master

115 Posts

Posted - 2013-11-02 : 18:18:09

This case statement is printing "--" when the phonenumber is null. How to print blank when phone number is null:

CASE WHEN PhoneNumber IS NOT NULL THEN (LEFT(PhoneNumber, 3) + '-' + SUBSTRING(PhoneNumber, 4, 3) + '-' + SUBSTRING(PhoneNumber, 7, 4)) ELSE '' END Phone,

Thanks.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-02 : 18:49:15

It is printing -- when the phone number is an empty string, rather than when it is NULL. Fix it like this:

select 
	CASE WHEN NULLIF(PhoneNumber,'') IS NOT NULL THEN (LEFT(PhoneNumber, 3) + '-' + SUBSTRING(PhoneNumber, 4, 3) + '-' + SUBSTRING(PhoneNumber, 7, 4)) 
	ELSE '' END Phone,

rama108
Posting Yak Master

115 Posts

Posted - 2013-11-02 : 18:59:36

James,
It works, thank you for your help. I was not expecting a response on a weekend. Wow! I always knew that I can count on the SQL Team for responses but even on a weekend? SQL Team is a no-nonsense team where there are no egos and one can get answers without any arguments, unlike other sql server sites.

Thank you and god bless you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-03 : 03:00:50

You can simply do this

COALESCE(STUFF(STUFF(PhoneNumber,4,0,'-'),8,'-'),'')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-03 : 10:26:02

Fixing up.

quote:
Originally posted by visakh16

You can simply do this

COALESCE(STUFF(STUFF(PhoneNumber,4,0,'-'),8,,0,'-'),'')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rama108
Posting Yak Master

115 Posts

Posted - 2013-11-03 : 11:14:48

Stuff really worked. Thanks to both.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources