SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Output of Query


Output of Query

Author
Message
Britt Cluff
Britt Cluff
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2365 Visits: 253
Good question, I had to read it carefully.

http://brittcluff.blogspot.com/
rfr.ferrari
rfr.ferrari
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2151 Visits: 13639
good question!!

:-)


rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life!

Mike Is Here
Mike Is Here
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1794 Visits: 513
stewartc-708166 (10/10/2011)
This is a good question, showing that care needs to be exercised when working with NULLs

Ajay.Kedar (10/10/2011)
I tried and got wrong in first select case :-)

But wanted to know that

How can we chack null in this case then?

As shown in the explanation, the way the CASE is structured will need to be changed somewhat, i.e.
SELECT CASE WHEN COL1 IS NULL THEN 'Is Null'  
WHEN COL1 = '0' THEN 'Is Zero'
WHEN COL1 = '1' THEN 'Is One'
WHEN COL1 = '2' THEN 'Is Two'
END
FROM
(
SELECT NULL AS Col1
UNION
SELECT '0' AS Col1
UNION
SELECT '1' AS Col1
UNION
SELECT '2' AS Col1
) TMP



This is the form that I always use nowadays, rarely are any of the select conditions that are simple value type check expressions.
Mattrick
Mattrick
Right there with Babe
Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)

Group: General Forum Members
Points: 728 Visits: 767
Great question.

Thanks,

Matt
Thomas Abraham
Thomas Abraham
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3813 Visits: 2256
Nice one! I almost missed it, but my spidy sense told me to look at the question again. Thanks.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Stuart Davies
Stuart Davies
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7375 Visits: 4817
Nice question - thanks

-------------------------------Posting Data Etiquette - Jeff Moden Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61795 Visits: 17954
sqlzealot-81 (10/10/2011)
I guess, you can achieve by the below way,

SELECT 
CASE IsNull(COL1,'') WHEN '' THEN 'Is Null'
WHEN '0' THEN 'Is Zero'
WHEN '1' THEN 'Is One'
WHEN '2' THEN 'Is Two'
END
FROM
(
SELECT NULL AS Col1
UNION
SELECT '0' AS Col1
UNION
SELECT '1' AS Col1
UNION
SELECT '2' AS Col1
) TMP



This works except you can't differentiate between null and empty strings because your isnull will now treat them both the same. In some cases this is ok but in others it isn't.

_______________________________________________________________

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 Modens 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)
BarbW
BarbW
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3341 Visits: 1130
I got an error when I executed the code - oh well Hehe
Toreador
Toreador
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3822 Visits: 8123
Sean Lange (10/10/2011)[hrThis works except you can't differentiate between null and empty strings because your isnull will now treat them both the same. In some cases this is ok but in others it isn't.


If it matters then you can do something like

CASE IsNull(COL1,'avaluethatwillnotappearinthedata') WHEN 'avaluethatwillnotappearinthedata' THEN 'Is Null'
Toreador
Toreador
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3822 Visits: 8123
Or:

SELECT 
CASE when Col1 is null then 'Is Null'
else case col1
WHEN '0' THEN 'Is Zero'
WHEN '1' THEN 'Is One'
WHEN '2' THEN 'Is Two'
END
end
FROM
(
SELECT NULL AS Col1
UNION
SELECT '0' AS Col1
UNION
SELECT '1' AS Col1
UNION
SELECT '2' AS Col1
) TMP


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search