CODEWITHSUNDEEP
phpmysqlsql-like
Gowri Shankar
Gowri Shankar

Reputation: 93

How to write query using LIKE to accept special characters also?

Am having one value in database as "123-456-789". My query is

SELECT * FROM table_name WHERE phone LIKE "%123456789%"

But it is not giving the result. Any idea

Upvotes: 0

Views: 52

Answers (3)

Colin Gell
Colin Gell

Reputation: 382

Try

SELECT * FROM table_name WHERE `phone` LIKE '%[0-9]' '_' '[0-9]' '_' [0-9]%'

You might also consider some OR statements

SELECT * FROM table_name WHERE `phone` LIKE '%[0-9]' '_' '[0-9]' '_' [0-9]%' OR '%[0-9]%'

Upvotes: 0

pr1nc3
pr1nc3

Reputation: 8358

Of course ,you excluded the dashes so it does not match the middle part. The logic of your query is "anything before my string and anything after" but the middle part should be the same.

SELECT * FROM table_name WHERE phone LIKE "%123-456-789%"

This will work.

But if you want to exclude dashes you can do something like:

SELECT * FROM table_name WHERE REPLACE(phone ,'-','') = "123456789"

If your input includes also dashes then you can remove them as well:

SELECT * FROM table_name WHERE REPLACE(phone ,'-','') = REPLACE('123456789','-','')

That way you can search a number with dashes or without but dashes will never be taken under consideration.

Upvotes: 1

Professor Abronsius
Professor Abronsius

Reputation: 33823

You could try using a regex directly within mySQL like this:

select * from `table_name` where `phone` regexp '[0-9]{3}-[0-9]{3}-[0-9]{3}';

Upvotes: 0

Related Questions