Cleaning out a field of Phone numbers in mySql

Question

In not a database guy but: I have mixed up data in a mySql database that I inherited.

Some Phone numbers are formatted (512) 555-1212 (call it dirty) Others 5125551212 (Call it clean)

I need a sqlstamet that says

UPDATE table_name
SET Phone="clean'(Some sort of cleaning code  -  regex?)
WHERE Phone='Dirty'

Answer 1

Unfortunately there's no regex replace/update in MySQL. If it's just parentheses and dashes and spaces then some nested REPLACE calls will do the trick:

UPDATE table_name
SET Phone = REPLACE(REPLACE(REPLACE(REPLACE(Phone, '-', ''), ')', ''), '(', ''), ' ', '')

Answer 2

To my knowledge you can't run a regexp to replace data during the update process. Only during the SELECT statement.

Your best bet is to use a scripting language that you're familiar with and read the table and change it that way. Basically by retrieving all the entries. Then using a string replace to match a simple regexp such as [^\d]* and remove those characters. Then update the table with the new value.

Also, see this answer: How to do a regular expression replace in MySQL?