LIKE and NULLs

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

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

  • Easy one.

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

  • 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

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

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

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

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

  • Nice and straightforwards - 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

  • This was removed by the editor as SPAM

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

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

  • 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. 😉

  • 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. 🙁

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

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

  • "Elementary, dear Watson!" 🙂

    Thanks, Gary!

  • 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 32 total)

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