October 9, 2011 at 9:54 pm
Comments posted to this topic are about the item Output of Query
October 10, 2011 at 12:03 am
Really good question. unfortunately i got it wrong. But got something new.
Thanks
🙂
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
October 10, 2011 at 12:14 am
I tried and got wrong in first select case 🙂
But wanted to know that
How can we chack null in this case then?
October 10, 2011 at 12:31 am
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
October 10, 2011 at 1:31 am
Thank you very much for your reply.
Yes, considering this example, it will work fine.
But what if I already have space in Col1.For that value too, it will show me 'Is null'.
October 10, 2011 at 2:06 am
Nice question, thanks!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 10, 2011 at 2:16 am
Good question.
But the real answer of course is "it depends" on what the setting of ansi_nulls is; it's on by default in an SSMS query window, so the given answer is correct there; it is incorrect in a context where ansi_nulls is off, which is the default for connections where the application or the connection provider doesn't explicitly it in the connection dialog (SMSS does, SQL 2000 QA did, but some things don't). Fortunately the ansi_nulls setting is an issue which will go away - the ability to change this setting it is a deprecated feature, there are important features which don't work if it is off (indexed views, indexes on computed columns, query notifications), and there will be a new version of SQL Server in which ansi_nulls is effectively always on and can't be switched off.
Tom
October 10, 2011 at 3:15 am
This was removed by the editor as SPAM
October 10, 2011 at 3:19 am
I've a feeling the behaviour was different in a previous version of SQLServer, as I think we were bitten by it when code that worked, stopped working. I don't have anything pre-2005 to test on though, so I may well be getting confused with something else 🙂
October 10, 2011 at 3:20 am
great 🙂
I am happy with this answer.
Thankd stewartc-708166.
October 10, 2011 at 5:26 am
Good question, I had to read it carefully.
http://brittcluff.blogspot.com/
October 10, 2011 at 5:41 am
good question!!
🙂
October 10, 2011 at 6:11 am
stewartc-708166 (10/10/2011)
This is a good question, showing that care needs to be exercised when working with NULLsAjay.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.
October 10, 2011 at 6:12 am
Great question.
Thanks,
Matt
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply