How to ignore characters on a MYSQL SELECT LIKE %...%

Question

I have a table with a phone column, where data may have spaces, dots,dashes or + signs between the numbers. I need to do a search with LIKE wildcards that ignore all those characters, for example:

a record may have phone as "+123-456 78.90"
a query looking for "6789" or any complete or incomplete sequence of proper digits in order should bring up that record.

Unfortunately I cant cleanup the original table to remove the non-digit characters and do a plain SELECT LIKE %...%.

MYSQL has functions to substitute/remove characters from strings, but can't find a way to use them inside a query with a widlcarded LIKE.

Any help will be much appreciated.

Which is the server side language ? PHP ? – Mar 24 '12 at 08:27 — , Mar 24 '12 at 08:27
It says mysql and Henry's talking about queries ;) – Martijn Jan 02 '14 at 15:19 — Martijn, Jan 02 '14 at 15:19

Hari · Accepted Answer · 2012-03-24T19:22:26.730

39

I see two ways doing this:

If you allow only a few extra characters than you can prepare a string which is stripped from these extra characters and you use the LIKE operator you normally would
```
select * from phoneTable where replace(replace(phone, '+', ''), '-', '') LIKE '%123%'
```
Of course you need as many replace calls as the number of allowed extra characters
You use regular expressions, let's say you are searching for pattern 123
```
select * from phoneTable where phone REGEXP '.*1[^0-9]*2[^0-9]*3'
```

edited Mar 24 '12 at 19:22

answered Mar 24 '12 at 09:04

Hari

4,514
2
31
33

Once adding the '%..%' to the first option, both of them do the job properly. Much thanks for your help. – Henry Mar 24 '12 at 16:05
You are right, I forgot out the wildcards, and I didn't try to run the queries. Answer corrected. – Hari Mar 24 '12 at 19:21
1

i'd guess first option is faster – Pavel K. Oct 30 '12 at 06:49

score 5 · Answer 2 · edited Jan 03 '14 at 08:18

5

I had the same problem with partnumbers and unwanted characters. My solution was

SELECT * FROM parts WHERE replace(partnumber, '-', '') LIKE '$searchterm%';

edited Jan 03 '14 at 08:18

Martijn

15,791
4
36
68

answered Apr 11 '13 at 21:15

Frank

59
1
1

5

how is this answer different from the accepted one? – Sp0T Jun 17 '15 at 06:48

How to ignore characters on a MYSQL SELECT LIKE %...%

2 Answers2

Linked

Related