Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CASE statement to include NULL value for blanks Expand / Collapse
Author
Message
Posted Tuesday, June 14, 2011 9:56 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:00 PM
Points: 476, Visits: 1,328
Hello -

I have a section of code that is part of a PROC, where the conditions being looked at is using a CASE statement, along with SUBSTRING conditions. The need that I have to account for is if a blank value were to be passed through, it needs to be treated as a NULL, and I am not sure how to account for this in the current construct. Here are a couple of examples of the current code...

Secondary Area Code
CASE LEFT(PhoneAlt,1) 
WHEN '(' THEN SUBSTRING(PhoneAlt,2,3)
ELSE LTRIM(LEFT(PhoneAlt,3))
END As [Provider!2!SecondaryAreaCode!element]

Secondary Phone Number
CASE 
WHEN SUBSTRING(PhoneAlt,5,1) = ')' THEN SUBSTRING(PhoneAlt,6,8)
WHEN SUBSTRING(PhoneAlt,4,1) = '-' THEN SUBSTRING(PhoneAlt,5,8)
ELSE LTRIM(SUBSTRING(PhoneAlt,4,8))
END As [Provider!2!SecondaryPhone!element]

Any ideas on how I would set an additional condition to account for a blank value to be accounted for as a NULL?

Many thanks in advance
Post #1125134
Posted Tuesday, June 14, 2011 10:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:14 PM
Points: 13,126, Visits: 11,964
nullif([your column here], '''')



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1125155
Posted Tuesday, June 14, 2011 10:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:00 PM
Points: 476, Visits: 1,328
Hi Sean - thanks for the reply. So just use your line in-between my final WHEN and before the ELSE?
Post #1125158
Posted Tuesday, June 14, 2011 10:22 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:00 PM
Points: 476, Visits: 1,328
OK - what if I need to make certain that there isn't just 1 space, but say 2 or 3 or 10 spaces? How would I incorporate LTRIM and RTRIM with your example?
Post #1125161
Posted Tuesday, June 14, 2011 10:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:14 PM
Points: 13,126, Visits: 11,964
Rich Yarger (6/14/2011)
OK - what if I need to make certain that there isn't just 1 space, but say 2 or 3 or 10 spaces? How would I incorporate LTRIM and RTRIM with your example?


like this

nullif(CASE LEFT(PhoneAlt,1) 
WHEN '(' THEN SUBSTRING(PhoneAlt,2,3)
ELSE LTRIM(LEFT(PhoneAlt,3))
END, '') As [Provider!2!SecondaryAreaCode!element]

Remember that string comparisons in sql will trim unless it for comparisons.

Look at the following:
declare @varchar varchar(10), @char char(10)

set @varchar = 'a'
set @char = 'a'

select DATALENGTH(@varchar), DATALENGTH(@Char)
where @varchar = @char

Here you can see that the actual data is not the same but when comparing they are equal.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1125169
Posted Tuesday, June 14, 2011 10:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:00 PM
Points: 476, Visits: 1,328
Awesome! Sean, thank you!

Post #1125173
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse