LIKE and NULLs

Question

LIKE and NULLs

chgn01

SSCarpal Tunnel

Points: 4365
More actions
February 12, 2014 at 8:33 pm

#304321

Comments posted to this topic are about the item LIKE and NULLs
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius
February 13, 2014 at 2:03 am

This was removed by the editor as SPAM

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

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

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 More actions · Answer 2

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: 13921 More actions · Answer 3

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

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

vijred Say Hey Kid Points: 681 More actions · Answer 4

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: 19505 More actions · Answer 5

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

Richard Warr SSCertifiable Points: 6967 More actions · Answer 6

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.

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 7

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: 287026 More actions · Answer 8

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

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

thisisfutile Hall of Fame Points: 3668 More actions · Answer 9

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 More actions · Answer 10

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

BarbW SSCarpal Tunnel Points: 4759 More actions · Answer 11

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

Revenant SSC-Forever Points: 42467 More actions · Answer 12

Revenant

SSC-Forever

Points: 42467

February 13, 2014 at 11:47 am

#1688886

"Elementary, dear Watson!" 🙂

Thanks, Gary!

TomThomson SSC Guru Points: 104773 More actions · Answer 13

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