CODEWITHSUNDEEP
mysql
NinjaBoy
NinjaBoy

Reputation: 3755

mysql - How to handle query search with special characters %(percent) and _(underscore)

I have 2 records in my database, Tom%Jerry and Ninja_Kids. One has a special character %(percent) and the other has _(underscore). I tried the following commands in mysql terminal.

SELECT * FROM CUSTOMER WHERE NAME LIKE '%%%';
SELECT * FROM CUSTOMER WHERE NAME LIKE '%_%';

Both of the commands returned all the records.

How am I gonna make these return the record that has either % or _.

I know that % is used in mysql like a reserved word but what if there are cases that a record contains special characters mentioned above.

BTW, aside from the 2 do you know other special characters that don't behave normal?

Please help. Thanks in advance.

Upvotes: 0

Views: 2034

Answers (2)

Sashi Kant
Sashi Kant

Reputation: 13465

Try using an escape character() before, like:-

select * from table where column like '\%\_' 


to match for string '%_'

Upvotes: 1

xdazz
xdazz

Reputation: 160893

You need to escape % and _. The doc.

SELECT * FROM CUSTOMER WHERE NAME LIKE '%\%%';
SELECT * FROM CUSTOMER WHERE NAME LIKE '%\_%';

Upvotes: 2

Related Questions