Use '=' or LIKE to compare strings in SQL?

There's the (almost religious) discussion, if you should use LIKE or '=' to compare strings in SQL statements.

• Are there reasons to use LIKE?
• Are there reasons to use '='?
• Performance? Readability?

9 Answers

LIKE and the equality operator have different purposes, they don't do the same thing:
= is much faster, whereas LIKE can interpret wildcards. Use = wherever you can and LIKE wherever you must.

SELECT * FROM user WHERE login LIKE 'Test%';

Sample matches:

TestUser1
TestUser2
TestU
Test

0

To see the performance difference, try this:

SELECT count(*)
FROM master..sysobjects as A
JOIN tempdb..sysobjects as B
on A.name = B.name
SELECT count(*)
FROM master..sysobjects as A
JOIN tempdb..sysobjects as B
on A.name LIKE B.name

Comparing strings with '=' is much faster.

1

In my small experience:

"=" for Exact Matches.

"LIKE" for Partial Matches.

1

There's a couple of other tricks that Postgres offers for string matching (if that happens to be your DB):

ILIKE, which is a case insensitive LIKE match:

select * from people where name ilike 'JOHN'

Matches:

• John
• john
• JOHN

And if you want to get really mad you can use regular expressions:

select * from people where name ~ 'John.*'

Matches:

• John
• Johnathon
• Johnny

2

Just as a heads up, the '=' operator will pad strings with spaces in Transact-SQL. So 'abc' = 'abc ' will return true; 'abc' LIKE 'abc ' will return false. In most cases '=' will be correct, but in a recent case of mine it was not.

So while '=' is faster, LIKE might more explicitly state your intentions.

For pattern matching use LIKE. For exact match =.

LIKE is used for pattern matching and = is used for equality test (as defined by the COLLATION in use).

= can use indexes while LIKE queries usually require testing every single record in the result set to filter it out (unless you are using full text search) so = has better performance.

LIKE does matching like wildcards char [*, ?] at the shell
LIKE '%suffix' - give me everything that ends with suffix. You couldn't do that with =
Depends on the case actually.

There is another reason for using "like" even if the performance is slower: Character values are implicitly converted to integer when compared, so:

declare @transid varchar(15)

if @transid != 0

will give you a "The conversion of the varchar value '123456789012345' overflowed an int column" error.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy