Find duplicate phone numbers (even if formatting differs)

Question

I am using the following query to select duplicate phone numbers from a table.

SELECT id, REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( phone, "+", '' ) , ")", '' ) , "(", '' ) , "-", '' ) , ' ', '' ) AS strippedPhone
FROM `customers`
GROUP BY strippedPhone
HAVING count( strippedPhone ) >1
LIMIT 0 , 300

It looks ugly and does not consider the possibilities of alphanumeric character on the field having the phone number.

Any better ways?

What do you want to do with the duplicates - remove them from the db? — Revent, Mar 28 '13 at 00:34
sql clr functions so you could use .net for normalizing phones will give you more tools — hagensoft, Mar 28 '13 at 00:37

score 0 · Answer 1 · edited May 23 '17 at 11:57

0

This StackOverflow post MySQL strip non-numeric characters to compare has a solution that uses the NumericOnly function.

http://venerableagents.wordpress.com/2011/01/29/mysql-numeric-functions/

edited May 23 '17 at 11:57

Community

1
1

answered Mar 28 '13 at 00:38

Revent

2,091
2
18
33

I came across that solution. Does not look that good either. Replace is much faster that that I think. – Starx Mar 28 '13 at 00:42

score 0 · Answer 2 · answered Mar 28 '13 at 00:49

In my opinion you would be to normalize the phone numbers before you stored them in the database. Any sql query that is able to compare phone numbers regardless of format is going to be highly inefficient. Where as if you if you reformat the phone numbers before inserting them into the database checking for duplicate numbers is trivial.

If you want to do this on an existing database you will have to normalize the data in the database first but that only has to be done once.

This is preventing the problem not solving. And I am aware of that. Thank you. — Starx, Mar 28 '13 at 00:50

Find duplicate phone numbers (even if formatting differs)

2 Answers2