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

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');

<kbd>[**Sql-Fiddle**][1]</kbd>

[1]:

[To see links please register here]

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

The underscore is the [wildcard in a `LIKE`][1] 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)][2]


<kbd>[**Demo**][3]</kbd>


[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

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:

[To see links please register here]

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][1].

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.


[1]:

[To see links please register here]

You can write the query as below:

```sql
SELECT * FROM Manager
WHERE managerid LIKE '\_%' escape '\'
AND managername LIKE '%\_%' escape '\';
```

it will solve your problem.

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:

[To see links please register here]