Format Phone Numbers in MySQL

问题:

I have a bunch of phone numbers in a DB that are formatted as such: [phone].

I'm needing them to look like 123-123-1234

Is there any sort of regex or something I can do in MySQL to quickly format all these phone numbers? Also, frustratingly, some are NOT formatted like that, so I couldn't just apply this to an entire column.

Thanks!

回答1:

A quick solution would be to run these two queries:

UPDATE table_name set PhoneCol = REPLACE(PhoneCol, '(', '');
UPDATE table_name set PhoneCol = REPLACE(PhoneCol, ') ', '-');

回答2:

Just write a small php script that loops through all the values and updates them. Making that change is pretty simple in php. Then just run an update on the row to overwrite the value.

回答3:

maybe a two pass solution.

1. strip out all non-numeric characters (and spaces)
2. inset the formatting characters '(',')', ' ', and '-' into the correct spots (or better yet, leave them off and format only during select on your reports.)