Output of Query

  • Sunil Chandurkar

    Right there with Babe

    Points: 791

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

  • Danny Ocean

    SSCertifiable

    Points: 6098

    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

    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

    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

    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: 258941

    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: 104772

    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

  • This was removed by the editor as SPAM

  • Toreador

    SSChampion

    Points: 11231

    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

    great 🙂

    I am happy with this answer.

    Thankd stewartc-708166.

  • Britt Cluff

    SSCertifiable

    Points: 5083

    Good question, I had to read it carefully.

    http://brittcluff.blogspot.com/

  • rfr.ferrari

    SSCertifiable

    Points: 6879

    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

    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

    Great question.

    Thanks,

    Matt

  • Thomas Abraham

    SSChampion

    Points: 10761

    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

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

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