Clean(Translat)ing Phone Numbers

Dealing with phone numbers in a database can be a lot of fun. Especially when there are open form applications that allow for anything to be inserted. Many times, you might encounter a column filled with data that looks like this:

PhoneNumber
(614)-555-0171
6145550175
614-555-0181
(614)-555-0122
614-555-0168
(614)-555-0143
(404)5550171
4045550189
(404)-555-0125
(404)-555-1234

I had a requirement to remove the parenthesis and the hyphens, to where these numbers only contained digits. I could obviously choose the traditional replace, doing something like this: REPLACE(REPLACE(REPLACE(PhoneNumber, '(', ''), ')', ''), '-', '')

What was that new function, again?

But then I remembered, not too long-ago Microsoft introduced something I’ve never been able to use. TRANSLATE. This was introduced in SQL Server 2017, and its syntax looks like this:
TRANSLATE ( inputString, characters, translations)

The important thing to remember is that the characters argument and the translations argument must have the same data type and length.

To achieve my goal of cleaning these characters…

SELECT PhoneNumber,
       TRANSLATE(PhoneNumber, '()-', '   ') AS TranslatedPhoneNumber,
	   REPLACE(TRANSLATE(PhoneNumber, '()-', '   '), ' ', '') AS ReplacedTranslatedPhoneNumber
FROM
(
    VALUES
        ('(614)-555-0171'),
        ('6145550175'),
        ('614-555-0181'),
        ('(614)-555-0122'),
        ('614-555-0168'),
        ('(614)-555-0143'),
        ('(404)5550171'),
        ('4045550189'),
        ('(404)-555-0125'),
		('(404)-555-1234')
) AS PhoneNumbers (PhoneNumber);

You’ll notice that second column still contains spaces. I did have to replace all the single spaces in the third column.

I was honestly thankful I finally had a purpose for this function. I’m sure there are several other great use cases. I would love to hear if you’ve been able to implement this anywhere.

Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *