LIKE and NULLs

Question

Post reply

LIKE and NULLs

Viewing 15 posts - 16 through 30 (of 32 total)

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

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 1

PChiragS

SSCarpal Tunnel

Points: 4965

February 13, 2014 at 4:32 pm

#1688946

nice and easy..

Owen White Ten Centuries Points: 1250 More actions · Answer 2

I have to be honest in that I cheated. Where is it documented that not using % assumes % at the end? I thought that this was a mistake but no one commented on it so perhaps I am ignorant to this obvious use of LIKE without some sort of wildcard. Can anyone shed some light on this?

Owen White

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

fsuoj (2/14/2014)
Where is it documented that not using % assumes % at the end?

The Insert "trims" the trailing spaces from the data.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

thisisfutile (2/14/2014)
fsuoj (2/14/2014)
Where is it documented that not using % assumes % at the end?
The Insert "trims" the trailing spaces from the data.

Not quite right. Trailing spaces are not trimmed during INSERTs. They are, in fact, stored in the data in the table. The trailing spaces are simply ignored for certain functionality (such as checks for equality) but comes into play just about everywhere else. The following code demonstrates...

DECLARE @QotD TABLE ( ID INT, Name VARCHAR(10) )

INSERT INTO @QotD

SELECT 1, NULL UNION all

SELECT 2, ' AA' UNION all

SELECT 3, ' AAA' UNION all

SELECT 4, 'AAA ' UNION all

SELECT 5, 'AA '

SELECT ID

,[Len] = LEN(Name)

,[DataLength] = DATALENGTH(NAME)

,Concatenated = '|'+Name+'|'

,[Right] = RIGHT(NAME,1)

,[Replace] = REPLACE(Name,' ','X'),RIGHT(NAME,1)

FROM @QotD

;

Results...

ID Len DataLength Concatenated Right Replace

----------- ----------- ----------- ------------ ----- ------------

1 NULL NULL NULL NULL NULL

2 3 3 | AA| A XAA

3 4 4 | AAA| A XAAA

4 3 10 |AAA | AAAXXXXXXX

5 2 3 |AA | AAX

(5 row(s) affected)

This is one of the huge gotcha's when converting right padded CHAR and NCHAR to VARCHAR and NVARCHAR for the sake of reducing bytes stored.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Owen White Ten Centuries Points: 1250 More actions · Answer 6

I still do not understand the answer to my question, where is the wildcard in the question why does it assume % at the end?

Owen White

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

Jeff Moden (2/16/2014)
thisisfutile (2/14/2014)
fsuoj (2/14/2014)
Where is it documented that not using % assumes % at the end?
The Insert "trims" the trailing spaces from the data.
Not quite right. Trailing spaces are not trimmed during INSERTs. They are, in fact, stored in the data in the table. The trailing spaces are simply ignored for certain functionality (such as checks for equality) but comes into play just about everywhere else. The following code demonstrates...

Thanks for taking the time to explain Jeff.

Before I posted my response to fsuoj, I simply did a LEN check on the data and based on the query you posted I was clearly wrong for assuming that trailing spaces weren't being inserted into the database. I completely forgot about DATALENGTH and would like to say I've learned something today because of it. Sadly, I've seen this command before but completely forgot about it. :-/

Again, thanks for taking the time to explain as I was passing along wrong information.

Owen White Ten Centuries Points: 1250 More actions · Answer 8

Owen White

Ten Centuries

Points: 1250

February 17, 2014 at 7:59 am

#1689531

You never answered my question!

Owen White

TomThomson SSC Guru Points: 104773 More actions · Answer 9

fsuoj (2/17/2014)
You never answered my question!

The reason it isn't documented as happening that that happens is that it doesn't happen.

Several people have explained what actually does happen, which doesn't include any assumed "%".

Tom

Owen White Ten Centuries Points: 1250 More actions · Answer 10

I am not trying to be difficult but no one in this thread has answered my question. How does SQL know to look for anything trailing the AAA and not to look for leading spaces. Trailing spaces are still characters. I would never use LIKE without using a wildcard.

Owen White

TomThomson SSC Guru Points: 104773 More actions · Answer 11

fsuoj (2/17/2014)
I am not trying to be difficult but no one in this thread has answered my question. How does SQL know to look for anything trailing the AAA and not to look for leading spaces. Trailing spaces are still characters. I would never use LIKE without using a wildcard.

When data is in char or varchar form trailing spaces are ignored in strings that are being matched against patterns. For like on char or varchar data, "@x LIKE <pattern>" implies that "@x+' ' like <pattern>" because there is this rule for char and varchar LIKE comparisons that trailing spaces are ignored.

This ignore trailing spces rule doesn't apply to unicode (nchar and nvarchar) data, only to ascii data.

This is part of the definition of the LIKE operator.

It isn't anything like introducing a "%" at the end of the pattern; doing that would mean that, for example, " 'the 'thing on the right' LIKE 'the thing' " would be true, which would be somewaht unuseful.

Tom

Owen White Ten Centuries Points: 1250 More actions · Answer 12

I want to thank you for this clear explanation. So when using the LIKE keyword 'AAA ' is returned because trailing spaces are ignored. 'AAA 1' would not be returned because the '1' causes this to not return a result explaining what you mean by % is not assumed. This is only valid for trailing spaces using char and varchar. Now I am clearly informed. Thank you!!!!

Owen White

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 13

fsuoj (2/17/2014)
I am not trying to be difficult but no one in this thread has answered my question. How does SQL know to look for anything trailing the AAA and not to look for leading spaces. Trailing spaces are still characters. I would never use LIKE without using a wildcard.

I use LIKE without using a wildcard all the time especially in special purpose splitters where more than one delimiter might be being checked for. For example...

...

WHERE t.N <= DATALENGTH(somecolumn)

AND SUBSTRING(somecolumn, t.N, 1) LIKE '[-;,.| ]'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 14

thisisfutile (2/17/2014)
Jeff Moden (2/16/2014)
thisisfutile (2/14/2014)
fsuoj (2/14/2014)
Where is it documented that not using % assumes % at the end?
The Insert "trims" the trailing spaces from the data.
Not quite right. Trailing spaces are not trimmed during INSERTs. They are, in fact, stored in the data in the table. The trailing spaces are simply ignored for certain functionality (such as checks for equality) but comes into play just about everywhere else. The following code demonstrates...
Thanks for taking the time to explain Jeff.
Before I posted my response to fsuoj, I simply did a LEN check on the data and based on the query you posted I was clearly wrong for assuming that trailing spaces weren't being inserted into the database. I completely forgot about DATALENGTH and would like to say I've learned something today because of it. Sadly, I've seen this command before but completely forgot about it. :-/
Again, thanks for taking the time to explain as I was passing along wrong information.

Absolutely my pleasure. Thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlnaive SSCoach Points: 17437 More actions · Answer 15

sqlnaive

SSCoach

Points: 17437

March 11, 2014 at 2:07 am

#1695832

Easy one. 🙂