Using REGEX to alter field data in a mysql query

Question

I have two databases, both containing phone numbers. I need to find all instances of duplicate phone numbers, but the formats of database 1 vary wildly from the format of database 2.

I'd like to strip out all non-digit characters and just compare the two 10-digit strings to determine if it's a duplicate, something like:

SELECT b.phone as barPhone, sp.phone as SPPhone FROM bars b JOIN single_platform_bars sp ON sp.phone.REGEX = b.phone.REGEX

Is such a thing even possible in a mysql query? If so, how do I go about accomplishing this?

EDIT: Looks like it is, in fact, a thing you can do! Hooray! The following query returned exactly what I needed:

SELECT b.phone, b.id, sp.phone, sp.id
FROM bars b JOIN single_platform_bars sp ON REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(b.phone,' ',''),'-',''),'(',''),')',''),'.','') = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(sp.phone,' ',''),'-',''),'(',''),')',''),'.','')

See the solution here, for mysql 8+: <stackoverflow.com/a/38789064/557432> — TomoMiha
– TomoMiha, Commented Jun 11 at 12:34

spencer7593 · Accepted Answer · 2012-08-30 20:32:53Z

MySQL doesn't support returning the "match" of a regular expression. The MySQL REGEXP function returns a 1 or 0, depending on whether an expression matched a regular expression test or not.

You can use the REPLACE function to replace a specific character, and you can nest those. But it would be unwieldy for all "non-digit" characters. If you want to remove spaces, dashes, open and close parens e.g.

REPLACE(REPLACE(REPLACE(REPLACE(sp.phone,' ',''),'-',''),'(',''),')','')

One approach is to create user defined function to return just the digits from a string. But if you don't want to create a user defined function...

This can be done in native MySQL. This approach is a bit unwieldy, but it is workable for strings of "reasonable" length.

SELECT CONCAT(IF(SUBSTR(sp.phone,1,1) REGEXP '^[0-9]$',SUBSTR(sp.phone,1,1),'')
             ,IF(SUBSTR(sp.phone,2,1) REGEXP '^[0-9]$',SUBSTR(sp.phone,2,1),'')
             ,IF(SUBSTR(sp.phone,3,1) REGEXP '^[0-9]$',SUBSTR(sp.phone,3,1),'')
             ,IF(SUBSTR(sp.phone,4,1) REGEXP '^[0-9]$',SUBSTR(sp.phone,4,1),'')
             ,IF(SUBSTR(sp.phone,5,1) REGEXP '^[0-9]$',SUBSTR(sp.phone,5,1),'')
       ) AS phone_digits
  FROM sp

To unpack that a bit... we extract a single character from the first position in the string, check if it's a digit, if it is a digit, we return the character, otherwise we return an empty string. We repeat this for the second, third, etc. characters in the string. We concatenate all of the returned characters and empty strings back into a single string.

Obviously, the expression above is checking only the first five characters of the string, you would need to extend this, basically adding a line for each position you want to check...

And unwieldy expressions like this can be included in a predicate (in a WHERE clause). (I've just shown it in the SELECT list for convenience.)

Yeah, this is super messy, but it works! I've added my final code to my above post. Thanks for the hand!

eggyal · Accepted Answer · 2012-08-30 18:09:49Z

1

MySQL doesn't support such string operations natively. You will either need to use a UDF like this, or else create a stored function that iterates over a string parameter concatenating to its return value every digit that it encounters.

answered Aug 30, 2012 at 18:09

eggyal

127k18 gold badges220 silver badges244 bronze badges

Collectives™ on Stack Overflow

Using REGEX to alter field data in a mysql query

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related