Output of Query

Question

Post reply

Output of Query

Sunil Chandurkar

Right there with Babe

Points: 791
More actions
October 9, 2011 at 9:54 pm

#244972

Comments posted to this topic are about the item Output of Query

Viewing 15 posts - 1 through 15 (of 38 total)

You must be logged in to reply to this topic. Login to reply

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 1

Really good question. unfortunately i got it wrong. But got something new.

Thanks

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

Ajay.Kedar Old Hand Points: 329 More actions · Answer 2

I tried and got wrong in first select case

But wanted to know that

How can we chack null in this case then?

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 3

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

Ajay.Kedar Old Hand Points: 329 More actions · Answer 4

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'.

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 5

Nice question, thanks!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

TomThomson SSC Guru Points: 104773 More actions · Answer 6

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

Toreador SSChampion Points: 11377 More actions · Answer 7

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

Ajay.Kedar Old Hand Points: 329 More actions · Answer 8

great

I am happy with this answer.

Thankd stewartc-708166.

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 9

Good question, I had to read it carefully.

http://brittcluff.blogspot.com/

rfr.ferrari SSCertifiable Points: 6879 More actions · Answer 10

good question!!

[font="Times New Roman"]rfr.ferrari[/font]
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 Hall of Fame Points: 3348 More actions · Answer 11

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 Ten Centuries Points: 1176 More actions · Answer 12

Mattrick

Ten Centuries

Points: 1176

October 10, 2011 at 6:12 am

#1392358

Great question.

Thanks,

Matt

Thomas Abraham SSChampion Points: 10761 More actions · Answer 13

Nice one! I almost missed it, but my spidy sense told me to look at the question again. Thanks.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Stuart Davies SSCoach Points: 19505 More actions · Answer 14

Nice question - thanks

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There 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

Output of Query

Cookies on SQLServerCentral