CODEWITHSUNDEEP
sql
senior
senior

Reputation: 2276

Sql query - search a varchar containing a wildcard character

If I execute this query:

select * from product where product_name like '%';

it gets all the products and that's expected.

But I want to get the products where the name contains the wildcard character '%'. How can I do that?

Upvotes: 0

Views: 5410

Answers (4)

Tim Schmelter
Tim Schmelter

Reputation: 460138

Presuming that you're using SQL-Server (thought you've mentioned it somewhere):

You have to escape the wildcard character % with brackets:

select *  from product  where product_name like '%[%]%';

Demo

Side-note: you have to do the same if you want to search underscores:

select *  from product  where product_name like '%[_]%';

because this means any single character.

Upvotes: 6

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

You can use ESCAPE:

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

Is a character that is put in front of a wildcard character to indicate that the wildcard should be interpreted as a regular character and not as a wildcard. escape_character is a character expression that has no default and must evaluate to only one character.

select *  
from product  
where product_name like '%|%%'  ESCAPE '|';

LiveDemo

Upvotes: 4

Dan
Dan

Reputation: 10680

While both Tim's and lad2025's answers are correct, a third option on SQL Server is to use CHARINDEX:

select *
from product where charindex('%', product_name) > 0

CHARINDEX does not use special characters for pattern matching, so you can use the % character in the search. However, if you use PATINDEX, it will behave similar to LIKE.

Upvotes: 3

Sameer Mirji
Sameer Mirji

Reputation: 2245

If the language you are looking for is Oracle (which it most seems like in your case), then

SELECT *  FROM product WHERE product_name LIKE '%\%%' ESCAPE '\';

should work.

Upvotes: 0

Related Questions