5

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.

Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72
eclipsis
  • 1,541
  • 3
  • 20
  • 56
  • You could write a nested SQL update statement with 3 cases (or however many number of formats) ... update the existing rows with the specific format desired. – Philo Jan 05 '16 at 22:26
  • 1
    You can use `REPLACE()` to replace specific characters. You can use `SUBSTR()` to extract specific ranges of characters. And you can use `CONCAT` to concatenate them into a new string. Which part of this isn't working for you? – Barmar Jan 05 '16 at 22:27
  • See http://stackoverflow.com/questions/16617847/cleaning-out-a-field-of-phone-numbers-in-mysql for how to remove the special characters from the phone number – Barmar Jan 05 '16 at 22:30
  • Possible duplicate of [MySQL output masking (i.e. phone number, SSN, etc. display formatting)](http://stackoverflow.com/questions/10112718/mysql-output-masking-i-e-phone-number-ssn-etc-display-formatting) – spiderman Jan 05 '16 at 22:34
  • Do you have only North American numbers? If not, your format will not work for some of the numbers. – Richard St-Cyr Jan 05 '16 at 22:35
  • Not a duplicate, as I want to update existing entries, not mask new ones. Also, they are NA numbers only. – eclipsis Jan 05 '16 at 22:49

1 Answers1

13

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.

Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72