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.

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
– Philo, Commented Jan 5, 2016 at 22:26
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
– Barmar, Commented Jan 5, 2016 at 22:27
See stackoverflow.com/questions/16617847/… for how to remove the special characters from the phone number — Barmar
– Barmar, Commented Jan 5, 2016 at 22:30
Possible duplicate of MySQL output masking (i.e. phone number, SSN, etc. display formatting) — spiderman
– spiderman, Commented Jan 5, 2016 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
– Richard St-Cyr, Commented Jan 5, 2016 at 22:35

Kamil Gosciminski · Accepted Answer · 2016-01-05 22:55:30Z

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.

First you cleanse your column by removing characters that aren't numbers using replace()
Then you take several parts of the string to separate them out using substr()
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.

Glad I could help :-) I don't like the way it's handled in MySQL - too much code, but if it works, we shouldn't discuss with it.

Collectives™ on Stack Overflow

MySQL column: Change existing phone numbers into specific format?

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related