CODEWITHSUNDEEP
sql-servert-sql
CodingYoshi
CodingYoshi

Reputation: 27009

In T-SQL, why is like operator not working for a variable but works for a literal?

Here is the script:

First script:

declare @a char(10) = 'AA%'
declare @b char(10) = 'AA001'

if @b LIKE @a 
     print 'Equals' 
else 
begin 
     print 'Not equals' 
end

It prints:

Not Equals

I was expecting it to print Equals. But the script below which does not use @a in the condition:

Second script:

declare @a char(10) = 'AA%'
declare @b char(10) = 'AA001'

if @b LIKE 'AA%' 
    print 'Equals' 
else 
begin 
    print 'Not equals' 
end

prints

Equals

So why does the first script consider them not-equal but the second script consider them equal?

Upvotes: 4

Views: 1738

Answers (1)

CodingYoshi
CodingYoshi

Reputation: 27009

The answer is actually simple but this is a big gotcha: It is because of the char type.

If we were to print out the 2 variables:

print @a
print @b

The output would be (dots mean empty space):

AA%.......       
AA001.....

So the first query does a like on the two variables and the trailing blanks make it not equal because according to MSDN, and logically speaking, the trailing blanks (empty spaces) are significant:

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. + A string comparison using a pattern that contains char and varchar data may not pass a LIKE comparison because of how the data is stored.

Second Query

The second query has the literal AA% whose implicit type is varchar and since AA001 (whose type is char) will be like AA%, it prints Equals. To reaffirm that the type of the literal is varchar, I used this query:

SELECT SQL_VARIANT_PROPERTY('AA%', 'BaseType')

which returns varchar.

Some More Info

Here is some more examples from MSDN for further clarity:

The following example passes a local char variable to a stored procedure and then uses pattern matching to find all of the employees whose last names start with a specified set of characters.

-- Uses AdventureWorks  

CREATE PROCEDURE FindEmployee @EmpLName char(20)  
AS  
SELECT @EmpLName = RTRIM(@EmpLName) + '%';  
SELECT p.FirstName, p.LastName, a.City  
FROM Person.Person p JOIN Person.Address a ON p.BusinessEntityID = a.AddressID  
WHERE p.LastName LIKE @EmpLName;  
GO  
EXEC FindEmployee @EmpLName = 'Barb';  
GO

In the FindEmployee procedure, no rows are returned because the char variable (@EmpLName) contains trailing blanks whenever the name contains fewer than 20 characters. Because the LastName column is varchar, there are no trailing blanks. This procedure fails because the trailing blanks are significant. However, the following example succeeds because trailing blanks are not added to a varchar variable.

-- Uses AdventureWorks 

CREATE PROCEDURE FindEmployee @EmpLName varchar(20)  
AS  
SELECT @EmpLName = RTRIM(@EmpLName) + '%';  
SELECT p.FirstName, p.LastName, a.City  
FROM Person.Person p JOIN Person.Address a ON p.BusinessEntityID = a.AddressID  
WHERE p.LastName LIKE @EmpLName;  
GO  
EXEC FindEmployee @EmpLName = 'Barb';

Here is the result set: 

FirstName LastName City
---------- -------------------- ---------------
Angela Barbariol Snohomish
David Barber Snohomish
(2 row(s) affected)

Upvotes: 8

Related Questions