LIKE and NULLs

  • chgn01

    Hall of Fame

    Points: 3531

    Comments posted to this topic are about the item LIKE and NULLs

    --------------------------------------
    ;-)“Everything has beauty, but not everyone sees it.” ― Confucius

  • free_mascot

    One Orange Chip

    Points: 27168

    Easy one.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Lokesh Vij

    SSChampion

    Points: 10836

    free_mascot (2/12/2014)


    Easy one.

    +1

    Thanks Gary!

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • Hany Helmy

    SSChampion

    Points: 13321

    I remember this one, it came before some months back.

  • vijred

    SSChasing Mays

    Points: 615

    Someone please help me understand the logic? The result changes if I use NVARRCHAR instead of VARCHAR.

    -Vijred (http://vijredblog.wordpress.com)

  • Stuart Davies

    SSCoach

    Points: 18874

    Nice and straightforwards - thanks

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]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

  • This was removed by the editor as SPAM

  • Richard Warr

    SSCertifiable

    Points: 6955

    vijred (2/12/2014)


    Someone please help me understand the logic? The result changes if I use NVARRCHAR instead of VARCHAR.

    Excellent point - if using NVARCHAR the query also returns the row corresponding to 'AAA '. Possibly something to do with the trailing space being recognised as a valid Unicode character but a discussion on that point would be more rewarding than people just telling us how easy the original question was.

    _____________________________________________________________________
    MCSA SQL Server 2012

  • patricklambin

    SSCrazy Eights

    Points: 9964

    In http://msdn.microsoft.com/en-us/library/ms187403.aspx , I found

    "The SET ANSI_PADDING setting does not affect the nchar, nvarchar, ntext, text, image, and large value. They always display the SET ANSI_PADDING ON behavior. This means trailing spaces and zeros are not trimmed"

    I think it is why there is a difference of behavior between nchar and char or between nvarchar and varchar datatypes.

  • Ed Wagner

    SSC Guru

    Points: 286958

    Thanks for the question. It made me intentionally consider the %, leading space and trailing space. It looked to simple to actually be simple and I was looking for the trick. 😉

  • thisisfutile

    Hall of Fame

    Points: 3484

    Apparently trailing spaces are not inserted but are trimmed by default. This is not clear by the OP's explanation. I guessed they were trimmed but I failed on the NULL issue, I thought it would be returned. 🙁

  • Michael.Neavill

    SSC Veteran

    Points: 216

    The trick was to look for what it wouldn't return. Which I learned after I answered it.

  • BarbW

    SSCarpal Tunnel

    Points: 4699

    I executed the same code on SQL 2012 SP1 and got the same results - 2,3,5

  • Revenant

    SSC-Forever

    Points: 42467

    "Elementary, dear Watson!" 🙂

    Thanks, Gary!

  • TomThomson

    SSC Guru

    Points: 104772

    Richard Warr (2/13/2014)


    vijred (2/12/2014)


    Someone please help me understand the logic? The result changes if I use NVARRCHAR instead of VARCHAR.

    Excellent point - if using NVARCHAR the query also returns the row corresponding to 'AAA '. Possibly something to do with the trailing space being recognised as a valid Unicode character but a discussion on that point would be more rewarding than people just telling us how easy the original question was.

    It's because Unicode data in a column of a table is stored including trailing spaces; if you substitute rtrim(Name) for Name in the comparison, it behaves the same way as for varchar. This is actually covered quite well on the LIKE page referenced in the explanation.

    Tom

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

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