In trying to consolidate my phone numbers, something broke, and the formatting of my numbers ended up broken.  Augh.  SQL Server to the rescue!

I started out exporting all my various contact lists, and loading them into a SQL Server table.  I then ran the following commands to make all my phone numbers valid again.  Then exported to a CSV and imported back into google contacts.

–remove all brackets
UPDATE google SET [Phone 1 – Value] = REPLACE([Phone 1 – Value], ‘)’, ”)
UPDATE google SET [Phone 1 – Value] = REPLACE([Phone 1 – Value], ‘(‘, ”)

–remove hyphens
UPDATE google SET [Phone 1 – Value] = REPLACE([Phone 1 – Value], ‘-‘, ”)

–replace leading 0011 with +
UPDATE google SET [Phone 1 – Value] = ‘+’ + SUBSTRING([Phone 1 – Value], 5, LEN([Phone 1 – Value])-1) WHERE [Phone 1 – Value] LIKE ‘0011%’
UPDATE google SET [Phone 1 – Value] = REPLACE([Phone 1 – Value], ‘::: 0011’, ‘::: +’)

–remove leading 0s
UPDATE google SET [Phone 1 – Value] = SUBSTRING([Phone 1 – Value], 2, LEN([Phone 1 – Value])-1) WHERE [Phone 1 – Value] LIKE ‘0%’
UPDATE google SET [Phone 1 – Value] = REPLACE([Phone 1 – Value], ‘::: 0’, ‘::: ‘)

–add a plus where it’s missing on existing intl codes.
UPDATE google SET [Phone 1 – Value] = ‘+ ‘ + [Phone 1 – Value] WHERE LEN([Phone 1 – Value]) > 10 AND [Phone 1 – Value] LIKE ‘61%’

–add the international code where required. Ignore local numbers (len < 8)
UPDATE google SET [Phone 1 – Value] = ‘+61 ‘ + [Phone 1 – Value] WHERE LEN([Phone 1 – Value]) > 8 AND SUBSTRING([Phone 1 – Value], 1, 1) IN (‘2′,’3′,’4′,’5′,’6′,’7′,’8′,’9’)
UPDATE google SET [Phone 1 – Value] = REPLACE([Phone 1 – Value], ‘::: 2’, ‘::: +61 2’)
UPDATE google SET [Phone 1 – Value] = REPLACE([Phone 1 – Value], ‘::: 3’, ‘::: +61 3’)
UPDATE google SET [Phone 1 – Value] = REPLACE([Phone 1 – Value], ‘::: 4’, ‘::: +61 4’)
UPDATE google SET [Phone 1 – Value] = REPLACE([Phone 1 – Value], ‘::: 5’, ‘::: +61 5’)
UPDATE google SET [Phone 1 – Value] = REPLACE([Phone 1 – Value], ‘::: 6’, ‘::: +61 6’)
UPDATE google SET [Phone 1 – Value] = REPLACE([Phone 1 – Value], ‘::: 7’, ‘::: +61 7’)
UPDATE google SET [Phone 1 – Value] = REPLACE([Phone 1 – Value], ‘::: 8’, ‘::: +61 8’)
UPDATE google SET [Phone 1 – Value] = REPLACE([Phone 1 – Value], ‘::: 9’, ‘::: +61 9’)