SqlServer: Like vs. "=" for matching strings

Answer 1

With the LIKE keyword you can match the field u.username against a specified pattern instead of a fixed "string".

Answer 2

You are correct. There is no benefit in using LIKE unless you are doing wild card matching. In addition, using it without wildcard could lead to the use of an inefficient queryplan.

Answer 3

Yes, as far as I know, using like without any wildcards is the same as using the = operator. are you sure the input parameter doesn't have wildcards in it?

Answer 4

Yes - you are right - it should only be used for wildcard matching. It should be used sparingly especially on very large tables on non-indexed fields as it can slow your queries WAY WAY down.

Answer 5

If no wildcards are used, then the difference is, that "=" makes an exact match, but LIKE will match a string with trailing spaces (from SSBO):

When you perform string comparisons with LIKE, all characters in the pattern string are significant, including 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.

Answer 6

If you're seeing this in other people's code maybe they intended to allow a person to pass in a string that included a pattern or wildcards.

Answer 7

Sunny almost got it right :)

Run the following in QA in a default install of SQL2005

select * from sysobjects where name = 'sysbinobjs   '
-- returns 1 row
select * from sysobjects where name like 'sysbinobjs   '
-- returns 0 rows

So, LIKE does not match on trailing spaces, on the query plan side both perform almost equally, but the '=' join performs a tiny bit better.

An additional thing you MUST keep in mind when using LIKE is to escape your string properly.

declare @s varchar(40) 
set @s = 'escaped[_]_%'

select 1 where 'escaped[_]_%'  like @s 
--Return nothing = BAD 

set @s = '_e_s_c_a_p_e_d_[___]___%' 

select 1 where 'escaped[_]_%'  like @s escape '_'
--Returns 1 = GOOD

In general people do not use LIKE for exact matching, because the escaping issues cause all sorts of complications and subtle bugs, people forget to escape and there is a world of pain.

But ... if you want a real exact match that is efficient, LIKE can solve the problem.

Say, you want to match username to "sam" and do not want to get "Sam" or "Sam " and unfortunately the collation of the column is case insensitive.

Something like the following (with the escaping added) is the way to go.

select * from sysobjects
WHERE name = 'sysbinobjs' and name COLLATE Latin1_General_BIN LIKE 'sysbinobjs'

The reason you do a double match is to avoid a table scan.

However ....

I think the varbinary casting trick is less prone to bugs and easier to remember.

Answer 8

LIKE is for wildcard matching, where as = (equals) is for an exact matches.

I also think it used for fields that have been catalogued by FULL TEXT CATALOGUES for hard core string comparisons.