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

User · Accepted Answer

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

User · Answer

Escape the percent sign    to make it part of your comparison value

User · Answer

To escape a character in sql you can use       EXAMPLE - USING ESCAPE CHARACTERS  It is important to understand how to  Escape Characters  when pattern matching  These examples deal specifically with escaping characters in Oracle   Let s say you wanted to search for a   or a   character in the SQL LIKE condition  You can do this using an Escape character   Please note that you can only define an escape character as a single character  length of 1    For example   SELECT   FROM suppliers WHERE supplier name LIKE      escape        This SQL LIKE condition example identifies the   character as an escape character  This statement will return all suppliers whose name is     Here is another more complicated example using escape characters in the SQL LIKE condition   SELECT   FROM suppliers WHERE supplier name LIKE  H     escape        This SQL LIKE condition example returns all suppliers whose name starts with H and ends in    For example  it would return a value such as  Hello     You can also use the escape character with the   character in the SQL LIKE condition   For example   SELECT   FROM suppliers WHERE supplier name LIKE  H     escape        This SQL LIKE condition example returns all suppliers whose name starts with H and ends in     For example  it would return a value such as  Hello       Reference  sql like

User · Answer

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

User · Answer

Select   from table where name like search criteria   if you are expecting the user to add their own wildcards

User · Answer

The easiest solution is to dispense with  like  altogether   Select    from table where charindex search criteria  name   gt  0   I prefer charindex over like   Historically  it had better performance  but I m not sure if it makes much of difference now

User · Answer

May be this one help     DECLARE  SearchCriteria VARCHAR 25  SET   SearchCriteria    employee  IF CHARINDEX       SearchCriteria    0 BEGIN SET  SearchCriteria          SearchCriteria       END SELECT    FROM Employee WHERE Name LIKE  SearchCriteria

User · Answer

You need to escape it  on many databases this is done by preceding it with backslash       So abc becomes abc     Your programming language will have a database-specific function to do this for you  For example  PHP has mysql escape string   for the MySQL database

[sql] SQL 'LIKE' query using '%' where the search criteria contains '%'

Examples related to sql

Examples related to sql-server

Examples related to sql-like