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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Case When with NULL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rama108
Posting Yak Master

109 Posts

Posted - 11/02/2013 :  18:18:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3588 Posts

Posted - 11/02/2013 :  18:49:15  Show Profile  Reply with Quote
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,
Go to Top of Page

rama108
Posting Yak Master

109 Posts

Posted - 11/02/2013 :  18:59:36  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/03/2013 :  03:00:50  Show Profile  Reply with Quote
You can simply do this

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3588 Posts

Posted - 11/03/2013 :  10:26:02  Show Profile  Reply with Quote
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


Go to Top of Page

rama108
Posting Yak Master

109 Posts

Posted - 11/03/2013 :  11:14:48  Show Profile  Reply with Quote
Stuff really worked. Thanks to both.
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