CODEWITHSUNDEEP
mysqlregexformat
Sara
Sara

Reputation: 133

Format Phone Numbers in MySQL

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!

Upvotes: 0

Views: 3097

Answers (4)

elhoeste
elhoeste

Reputation: 11

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.

Upvotes: 0

Muleskinner
Muleskinner

Reputation: 14468

A quick solution would be to run these two queries:

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

Upvotes: 4

Randy
Randy

Reputation: 16677

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.)

Upvotes: 1

Landon
Landon

Reputation: 4108

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.

Upvotes: 2

Related Questions