MSSQL Standardize Phone Numbers

Recently I had to clean up a database of wildly varying phone number formats. Hopefully, it saves someone else some time.

# Replace spaces with dashes to eliminate the simple stuff first for speed
update orders set phone=REPLACE(Phone, ' ', '-') where phone not like '[0-9]{3}-[0-9]{3}-[0-9]{4}'

# Insert the 2nd missing dash
update orders set phone=STUFF(Phone, 7, 0, '-') where phone like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

# Remove all formatting characters, parse the number as a bigint and if parsing is successful, format
update orders set Phone=FORMAT(TRY_CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Phone, '`', ''), '/', ''), '_', ''), '''', ''), ' ', ''), '.', ''), ')', ''), '(', ''), '-', '') AS bigint), '000-000-0000') from orders where phone not like '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' and Phone is not null and TRY_CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Phone, '`', ''), '/', ''), '_', ''), '''', ''), ' ', ''), '.', ''), ')', ''), '(', ''), '-', '') AS bigint) is not null

# Remove the country code digit
update orders set Phone=SUBSTRING(phone, 2, 8000) from orders where phone like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'