SQL 'LIKE' query using '%' where the search criteria contains '%'

Question

I have an SQL query as below.

Select * from table 
where name like '%' + search_criteria + '%' 

If search_criteria = 'abc', it will return data containing xxxabcxxxx which is fine.

But if my search_criteria = 'abc%', it will still return data containing xxxabcxxx, which should not be the case.

How do I handle this situation?

Answer 1

If you want a % symbol in search_criteria to be treated as a literal character rather than as a wildcard, escape it to [%]

... where name like '%' + replace(search_criteria, '%', '[%]') + '%' 

Answer 2

Use an escape clause:

select *
  from (select '123abc456' AS result from dual
        union all
        select '123abc%456' AS result from dual
       )
  WHERE result LIKE '%abc\%%' escape '\'

Result

123abc%456

You can set your escape character to whatever you want. In this case, the default '\'. The escaped '\%' becomes a literal, the second '%' is not escaped, so again wild card.

See List of special characters for SQL LIKE clause

Answer 3

The easiest solution is to dispense with "like" altogether:

Select * 
from table
where charindex(search_criteria, name) > 0

I prefer charindex over like. Historically, it had better performance, but I'm not sure if it makes much of difference now.