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

sql sql-server sql-like
pratik picture pratik · May 29, 2012 · Viewed 375.4k times · Source

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

Alex K. picture Alex K. · May 29, 2012

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, '%', '[%]') + '%'

Related questions

SQL- Ignore case while searching for a string

I have the following data in a Table PriceOrderShipped PriceOrderShippedInbound PriceOrderShippedOutbound In SQL I need to write a query which searches for a string in a table. While searching for a string it should ignore case. For the below mentioned …

Read More
SQL Server: use CASE with LIKE

I am pretty new to SQL and hope someone here can help me with this. I have a stored procedure where I would like to pass a different value depending on whether a column contains a certain country or not. …

Read More
SQL SELECT WHERE string ends with Column

I have this table in SQL Server 2012: Id INT DomainName NVARCHAR(150) And the table have these DomainName values google.com microsoft.com othersite.com And this value: mail.othersite.com and I need to select the rows where the string …

Read More