-1

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

NavyCody
  • 492
  • 2
  • 10

3 Answers3

1

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

Adrian
  • 380
  • 3
  • 13
Rahul Khandelwal
  • 71
  • 6
  • 1
    used instr to get index and the used subtring to get the match portion. This would do for time being. Thanks @rahul – NavyCody May 08 '18 at 07:38
0

You may use a CASE expression here:

SELECT
    Name,
    CASE WHEN Phone REGEXP '123456' THEN '123456'
         WHEN Phone REGEXP '565834' THEN '565834' END AS Matched,
    Phone
FROM customers
WHERE
    phone REGEXP '123456|565834';

Note that you don't necessarily need to use REGEXP in this case, LIKE probably would have sufficed. But using REGEXP is easier to read IMO, and also it lets you handle more complex logic, should you need to in the future.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Hi @Tim Biegelsien. Its good suggestion. What if i have any array of 1000 phone numbers to be looked up and accordingly get the matched portion. Certainly we can construct a case when statement string and add to the query dynamically but is there any other approach ? – NavyCody May 08 '18 at 07:02
0

If you're not constrained to only use mysql, then may try with shell script + mysql to pass the map values and search - Not sure understood the requirement correctly.

search="5672C";
mysql <connection details> -e "select fields, \"$search\" as matched from customers where phone like \"%$search%\";"

We can loop the map strings and achieve.

Muthukumar
  • 1