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.
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');
This question is related to
sql
sql-server
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)
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
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
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
You can write the query as below:
SELECT * FROM Manager
WHERE managerid LIKE '\_%' escape '\'
AND managername LIKE '%\_%' escape '\';
it will solve your problem.
Source: Stackoverflow.com