MySQL fix some phone numbers

This is long and convoluted, but basically, it takes unknown inputs of phone numbers from a table:

5551212

555-1212

212555-1212

212-555-1212

and makes them all

2125551212

select Concat(if(length(PhoneNumber)< 10, "212", ""), replace(PhoneNumber, "-","")) FROM AddressBook;

update addressbook set PhoneNumber=Concat(if(length(PhoneNumber)< 10, "212", ""), replace(PhoneNumber, "-",""));

Excel Fix a Phone list

Occasionally, you'll have a list of phone numbers that need a little fixing. This code fixes many things as long as the last 8 digits are like 555-1212 and there's three digits of area code either preceded by "(" or flush left as in "(555)555-1212" or "555-555-1212". The result will always be formatted as (555) 555-1212, unless an area code is given.

A2 represents where the broken number is stored.


=CONCATENATE("(",IF(LEN(A2)<9,"555",IF(LEFT(A2,1)="(",MID(A2,2,3),LEFT(A2,3))),") ",RIGHT(A2,8))


You should change 555 to your local area code.