'Advanced' Mysql query using REGEXP

Question

I have two relational tables, t1 has the column call_number and t2 has phone_number.

Example tables:

+----+-------------+
| id | call_number |
+----+-------------+
| 1  | 36202665476 |
+----+-------------+

+----+-----------------+
| id |   phone_number  |
+----+-----------------+
| 1  | +36 20 266-5476 |
+----+-----------------+

I have to write a query which give back these two "matching" rows, using regexp, without any modification on the database.

SELECT t1.*,t2.* 
FROM t1,t2 
WHERE 
t1.call_number REGEXP concat( t2.phone_number, '[^0-9]')

So in the query I have to remove unnecessary characters (from t2.phone_number) THEN compare that "trimmed" column with t1.call_number to find a match.

Any advice comes in handy.

Thanks

Possible duplicate of [MySQL Regex Search and replace](http://stackoverflow.com/questions/22421840/mysql-regex-search-and-replace) — Stephan, Dec 30 '15 at 13:21
@Stephan This is nothing like that. He's not trying to replace the regexp. — Barmar, Dec 30 '15 at 13:22
@Barmar His intent is clear: remove any character that is not a digit (ie replace any character that is not a digit with the empty char) then compare the resulting string with the values in `t1`. — Stephan, Dec 30 '15 at 13:27
oh, you think that's what he meant by `concat(t2.phone_number, '[^0-9]')`. You could be right, I never thought of that. — Barmar, Dec 30 '15 at 13:34

jpw · Accepted Answer · 2015-12-30T13:26:07.737

2

You could just replace the surplus characters in the join condition like this:

select *
from t1 
join t2 
  on t1.call_number = replace(replace(replace(t2.phone_number, ' ',''), '-',''),'+','')

Of course you would have to know in advance and account for all the characters that should be removed.

Sample SQL Fiddle

Sample result showing the match:

| id | call_number | id |    phone_number |
|----|-------------|----|-----------------|
|  1 | 36202665476 |  1 | +36 20 266-5476 |

edited Dec 30 '15 at 13:26

answered Dec 30 '15 at 13:19

jpw

44,361
6
66
86

2

Thank you all answers, I just wanted to filter out unnecessary characters with regexp, but that did the job: on t1.call_number = replace(replace(replace(t2.phone_number, ' ',''), '-',''),'+','') So Regexp no needed – Rajnai Rafael Dec 30 '15 at 13:51

Mathew · Answer 2 · 2015-12-30T13:33:47.700

1

Then execute this query to fetch the numbers that have matching values:

SELECT t1.*
FROM `t1`,`t2`
WHERE t1.`call_number` REGEXP REPLACE(REPLACE(REPLACE(t2.`phone_number`, ' ', ''),'-', ''),'+', '');

edited Dec 30 '15 at 13:33

answered Dec 30 '15 at 13:18

Mathew

296
1
7

1

He said **without any modification of the database**. – Barmar Dec 30 '15 at 13:19
is the id not the relation between the tables ? – Bernd Buffen Dec 30 '15 at 13:22
@BerndBuffen The relation between the tables is the phone numbers. – Barmar Dec 30 '15 at 13:26
@Barmar I have edited the answer to ensure the database is not modified. – Mathew Dec 30 '15 at 13:34

score 1 · Answer 3 · answered Dec 30 '15 at 13:20

Call REPLACE in the argument to CONCAT to remove the special characters.

WHERE t1.call_number REGEXP CONCAT(REPLACE(REPLACE(REPLACE(t2.phone_number, ' ', ''),'-', ''),'+', ''), '[^0-9]')

Note that concatenating [^0-9] means it will only find a match where the matching number is followed by a non-numeric character. So it won't match the two example rows you showed, because there's nothing after the call_number.

score 0 · Answer 4 · answered Dec 30 '15 at 13:37

0

You can use REGEXP_REPLACE if your MySQL / MariaDB not to old

MariaDB > SELECT REGEXP_REPLACE('ab12cd','[^0-9]','') AS only_digits;
+-------------+
| only_digits |
+-------------+
| 12          |
+-------------+
1 row in set (0.00 sec)

answered Dec 30 '15 at 13:37

Bernd Buffen

14,525
2
24
39

'Advanced' Mysql query using REGEXP

4 Answers4