MySQL column: Change existing phone numbers into specific format?

Question

I have a MySQL column that contains phone numbers, the problem is that they're in different formats, such as:

• 2125551212
• 212-555-1212
• (212)5551212

I'd like to know if it's possible to take the existing 10 digits, remove the formatting, and change them all to this format: (212) 555-1212

Not a duplicate, as I'm looking to update several thousand entries instead of masking new entries.

Answer 1

Unfortunately, no REGEXP_MATCHES() or TRANSLATE() function comes with standard MySQL installation (they do with Postgres), so you could do this a way which I find really dirty, but it works.

1. First you cleanse your column by removing characters that aren't numbers using replace()
2. Then you take several parts of the string to separate them out using substr()
3. Finally, you concatenate them adding symbols between your substrings with concat()

If you have any more characters that you need truncate, just add another replace() on top of 3 already existing.

Sample data

create table nums ( num text );

insert into nums values 
    ('2125551212'),
    ('212-555-1212'),
    ('(212)5551212');

Query formatting your data

select 
  num, 
  concat('(',substr(num_cleansed,1,3),') ',substr(num_cleansed,4,3),'-',substr(num_cleansed,7)) AS num_formatted
from (
  select 
    num, 
    replace(replace(replace(num,'(',''),')',''),'-','') as num_cleansed
  from nums
  ) foo

Result

num             num_formatted
2125551212      (212) 555-1212
212-555-1212    (212) 555-1212
(212)5551212    (212) 555-1212

Click here SQLFiddle to preview output.

I'm leaving UPDATE statement as a homework for the reader.