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

sql comparison
guerda picture guerda · Feb 5, 2009 · Viewed 601.1k times · Source

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?

Answer

soulmerge picture soulmerge · Feb 5, 2009

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

Related questions

Compare dates in MySQL

I want to compare a date from a database that is between 2 given dates. The column from the database is DATETIME, and I want to compare it only to the date format, not the datetime format. SELECT * FROM `players` WHERE …

Read More
Stored Procedures Vs. Views

I have used both but what I am not clear is when I should prefer one over the other. I mean I know stored procedure can take in parameters...but really we can still perform the same thing using Views …

Read More
How to compare varbinary in where clause in SQL Server

I want to compare varbinary type with byte array. I have tried so far: DECLARE @data AS NVARCHAR(MAX)='4283' Select * from table1 Where bindayData=CAST(@data AS VARBINARY) But this does not work. I note one strange behaviour …

Read More