Why does using an Underscore character in a LIKE filter give me all the results

Question

I wrote the below SQL query with a LIKE condition   SELECT   FROM Manager WHERE managerid LIKE      AND managername LIKE         In the LIKE I want to search for any underscores      but I know that my columns  data has no underscore characters    Why does the query give me all the records from the table    Sample data   create table Manager      id int      managerid varchar 3       managername varchar 50          insert into Manager id managerid managername values 1  A1   Mangesh    insert into Manager id managerid managername values 2  A2   Sagar    insert into Manager id managerid managername values 3  C3   Ahmad    insert into Manager id managerid managername values 4  A4   Mango    insert into Manager id managerid managername values 5  B5   Sandesh      Sql-Fiddle

User · Answer

In case people are searching how to do it in BigQuery:

An underscore "_" matches a single character or byte.
You can escape "\", "_", or "%" using two backslashes. For example, "\%". If you are using raw strings, only a single backslash is required. For example, r"\%".

WHERE mycolumn LIKE '%\\_%'

Source: https://cloud.google.com/bigquery/docs/reference/standard-sql/operators

User · Answer

The underscore is the wildcard in a LIKE query for one arbitrary character   Hence LIKE     means  give me all records with at least one arbitrary character in this column    You have to escape the wildcard character  in sql-server with    around   SELECT m    FROM Manager m  WHERE m managerid    LIKE         AND   m managername  LIKE           See  LIKE  Transact-SQL   Demo

User · Answer

Modify your WHERE condition like this   WHERE mycolumn LIKE        ESCAPE       This is one of the ways in which Oracle supports escape characters  Here you define the escape character with the escape keyword  For details see this link on Oracle Docs   The     and     are wildcards in a LIKE operated statement in SQL   The   character looks for a presence of  any  one single character  If you search by columnName LIKE   abc   it will give you result with rows having  aabc    xabc    1abc     abc  but NOT  abc    abcc    xabcd  and so on   The     character is used for matching 0 or more number of characters  That means  if you search by columnName LIKE   abc   it will give you result with having  abc    aabc    xyzabc  and so on  but no  xyzabcd    xabcdd  and any other string that does not end with  abc    In your case you have searched by        This will give all the rows with that column having one or more characters  that means any characters  as its value  This is why you are getting all the rows even though there is no   in your column values

User · Answer

As you want to specifically search for a wildcard character you need to escape that  This is done by adding the ESCAPE clause to your LIKE expression  The character that is specified with the ESCAPE clause will  invalidate  the following wildcard character    You can use any character you like  just not a wildcard character   Most people use a   because that is what many programming languages also use  So your query would result in   select    from Manager where managerid LIKE       escape     and managername like        escape        But you can just as well use any other character   select    from Manager where managerid LIKE       escape     and managername like        escape        Here is an SQLFiddle example  http   sqlfiddle com   6 63e88 4

User · Answer

Underscore is a wildcard for something  for example  A    will look for all match that Start whit  A  and have minimum 1 extra character after that

User · Answer

You can write the query as below   SELECT   FROM Manager WHERE managerid LIKE       escape     AND managername LIKE        escape        it will solve your problem

[sql] Why does using an Underscore character in a LIKE filter give me all the results?

Examples related to sql

Examples related to sql-server