February 12, 2014 at 8:33 pm
Comments posted to this topic are about the item LIKE and NULLs
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius
February 12, 2014 at 9:35 pm
Easy one.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
February 12, 2014 at 10:40 pm
free_mascot (2/12/2014)
Easy one.
+1
Thanks Gary!
~ Lokesh Vij
Link to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
February 12, 2014 at 11:24 pm
I remember this one, it came before some months back.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
February 12, 2014 at 11:42 pm
Someone please help me understand the logic? The result changes if I use NVARRCHAR instead of VARCHAR.
-Vijred (http://vijredblog.wordpress.com)
February 13, 2014 at 12:56 am
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
February 13, 2014 at 2:03 am
This was removed by the editor as SPAM
February 13, 2014 at 2:18 am
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.
February 13, 2014 at 4:07 am
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.
February 13, 2014 at 5:17 am
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. 😉
February 13, 2014 at 7:26 am
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. 🙁
February 13, 2014 at 9:27 am
The trick was to look for what it wouldn't return. Which I learned after I answered it.
February 13, 2014 at 9:40 am
I executed the same code on SQL 2012 SP1 and got the same results - 2,3,5
February 13, 2014 at 11:47 am
"Elementary, dear Watson!" 🙂
Thanks, Gary!
February 13, 2014 at 12:36 pm
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