Search code examples
mysqlregexp-substr

Mysql regex search over an array of phone numbers

Need help in executing a query over a customer database on a field named phone.

select * from customers where phone REGEXP '123456|565834'

I need a way to select the matched portion of regex matched in select clause. The final results should be something like 

 Name      Matched      Phone

 Naveen    123456      12345678
 Naveen2   123456     123456789
 Arun      565834       9565834
 Arun2     565834      10565834

P.S. This has to be one query and there is no other unique key to be grouped by with

Solution

  • Use INSTR function of MySQL. Ex. INSTR(regex, phone)

    SELECT SUBSTRING('123456|565834',INSTR('123456|565834',phone),10)  
FROM customers 
WHERE phone REGEXP '123456|565834';

    Doc: INSTR function