Query regarding LIKE based on query result

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query regarding LIKE based on query result

Author

Topic

anujpratap84
Starting Member

45 Posts

Posted - 2012-05-09 : 06:55:30

Hi All,

Can anyone tell me why below query is showing 'NO' in 7th column.

DECLARE @A Char(20)
DECLARE @B VarChar(20)

SET @A = 'X'
SET @B = 'X'

SELECT
@A AS A,
@B AS B,
LEN(@A) AS 'A Length',
LEN(@B) AS 'B Length',
CASE WHEN @A = @B THEN 'Yes' ELSE 'No' END AS 'A = B',
CASE WHEN @A LIKE @B THEN 'Yes' ELSE 'No' END AS 'A LIKE B',
CASE WHEN @B LIKE @A THEN 'Yes' ELSE 'No' END AS 'B LIKE A',
CASE WHEN @B = @A THEN 'Yes' ELSE 'No' END AS 'B = A'

Thanks in advance..

Anuj Pratap Singh

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-09 : 07:01:39

@A is has X followed by 19 spaces (because the data type CHAR(20) has 20 characters always, the unused spots padded with spaces. On the other hand @B has exactly one character. So in the 7th column you are asking this, which of course, returns No:


WHEN 'X' LIKE 'X                  ' THEN 'Yes'
	     ELSE 'No'

Many other operations and functions LEN, ISNULL etc ignores the trailing spaces. Instead of LEN function, if you use DATALENGTH function, it will include trailing spaces.

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-09 : 07:07:57

Execute this
Change the datatypes.

DECLARE @A varChar(20)
DECLARE @B VarChar(20)

SET @A = 'X'
SET @B = 'X'

SELECT
@A AS A,
@B AS B,
LEN(@A) AS 'A Length',
LEN(@B) AS 'B Length',
CASE WHEN @A = @B THEN 'Yes' ELSE 'No' END AS 'A = B',
CASE WHEN @A LIKE @B THEN 'Yes' ELSE 'No' END AS 'A LIKE B',
CASE WHEN @B LIKE @A THEN 'Yes' ELSE 'No' END AS 'B LIKE A',
CASE WHEN @B = @A THEN 'Yes' ELSE 'No' END AS 'B = A'

anujpratap84
Starting Member

45 Posts

Posted - 2012-05-11 : 04:25:18

Agree with you but why
WHEN 'X ' LIKE 'X' THEN 'Yes'
ELSE 'No'
returns Yes

quote:
Originally posted by sunitabeck

@A is has X followed by 19 spaces (because the data type CHAR(20) has 20 characters always, the unused spots padded with spaces. On the other hand @B has exactly one character. So in the 7th column you are asking this, which of course, returns No:

WHEN 'X' LIKE 'X ' THEN 'Yes'
ELSE 'No'
Many other operations and functions LEN, ISNULL etc ignores the trailing spaces. Instead of LEN function, if you use DATALENGTH function, it will include trailing spaces.

Anuj Pratap Singh

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-11 : 06:55:59

quote:
Originally posted by anujpratap84

Agree with you but why
WHEN 'X ' LIKE 'X' THEN 'Yes'
ELSE 'No'

returns Yes

Anuj Pratap Singh

This is specifically the way LIKE clause is set up. I am quoting from this link:

"When you perform string comparisons by using LIKE, all characters in the pattern string are significant. This includes leading or trailing spaces. If a comparison in a query is to return all rows with a string LIKE 'abc ' (abc followed by a single space), a row in which the value of that column is abc (abc without a space) is not returned. However, trailing blanks, in the expression to which the pattern is matched, are ignored. If a comparison in a query is to return all rows with the string LIKE 'abc' (abc without a space), all rows that start with abc and have zero or more trailing blanks are returned."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources