Search phone number from MYSQL DB

Question

I have a table with phone column. Phones in it looks like this +1 (123) 456-3780. If user input in search field "35" i should show him all users with phones:

+3 (534) 000-1010

+1 (350) 000-9090

+1 (003) 534-2343

+1 (001) 223-5323 etc.

i have tried to do something like that

SELECT * FROM `mytable` WHERE `phone` LIKE '%3%5'

but this is not the best solution, beacause query get other phones like that +3 (000) 000-500.

I would like to use RLIKE or something like that, but do not know how create needed regular expression and how should it look?

score 4 · Accepted Answer · answered Nov 05 '12 at 11:51

4

You need a [() -]* between each input character:

SELECT * FROM `mytable`
WHERE `phone` REGEXP '3[() -]*5'

This allows punctuation to exist inside your digit "string" but nothing else.

answered Nov 05 '12 at 11:51

Lightness Races in Orbit

378,754
76
643
1,055

Or just a non-digit class, since the input is free-form. – Álvaro González Nov 05 '12 at 11:56
1

Any input with different punctuation would be invalid (whether it's actually validated in the OP's code or not) and as such I would expect all lookups on it to fail. – Lightness Races in Orbit Nov 05 '12 at 11:58

score -1 · Answer 2 · edited Dec 03 '14 at 01:12

-1

REGEXP '^(.{2})+(\.{4}[-].{4})$' this pattern is for phone numbers with ()

example: (00)0000-0000

edited Dec 03 '14 at 01:12

answered Dec 03 '14 at 00:30

Matt Bidas

1

Search phone number from MYSQL DB

2 Answers2

Linked