Use or LIKE to compare strings in SQL

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   http   support microsoft com kb 316626

User · Answer

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

User · Answer

For pattern matching use LIKE  For exact match

User · Answer

In my small experience       for Exact Matches    LIKE  for Partial Matches

[sql] Use '=' or LIKE to compare strings in SQL?

Examples related to sql

Examples related to comparison