Use '=' or LIKE to compare strings in SQL?
SqlComparisonSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
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.
Solution 3 - Sql
In my small experience:
"=" for Exact Matches.
"LIKE" for Partial Matches.
Solution 4 - Sql
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
Solution 5 - Sql
For pattern matching use LIKE. For exact match =.
Solution 6 - Sql
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.
Solution 7 - Sql
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.
Solution 8 - Sql
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.
Solution 9 - Sql
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.