I have a bunch of phone numbers in a DB that are formatted as such: (999) 123-3456.
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!
A quick solution would be to run these two queries:
UPDATE table_name set PhoneCol = REPLACE(PhoneCol, '(', ''); UPDATE table_name set PhoneCol = REPLACE(PhoneCol, ') ', '-'); 
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.
maybe a two pass solution.
I had a similar problem, but increased by the reason that some phones had the format with the dashes and others did not and this was the command that helped me to update the formats of the numbers that did not have the hyphens.
Phone before the command: 1234567890 Phone after command: 123-456-7890 The phone field is called phone_number and is a VARCHAR
The command I used is:
UPDATE database.table SET phone_number = concat(SUBSTRING(phone_number,1,3) , '-' , SUBSTRING(phone_number,4,3) , '-' , SUBSTRING(phone_number,7,4)) WHERE LOCATE('-', phone_number) = 0; I think your command could be like this:
UPDATE database.table SET phone_number = concat(SUBSTRING(phone_number,2,3) , '-' , SUBSTRING(phone_number,7,8));
I would remove the WHERE clause under the assumption that all phones would be formatted with the (). Also, the second string of characters would start from position 7 because there appears to be a space after the parentheses.
