Cleanup Mobile Phone Numbers using SQL and Automation Studio

Salesforce Marketing Cloud’s Mobile Connect application requires the standardisation of mobile phone numbers in order for it to successfully deploy SMS or MMS sends to contacts. The application expects that mobile numbers are normalized which ensures that the number is in the correct format.

Depending how you collect subscriber contact information as well as validation measures put in place on the front end, your database may contain mobile phone numbers in various formats and contain illegal characters. These would not be valid and SFMC would not send to these numbers.

The SQL snippet below is an example of how you can manipulate and transform data that you have in CRM to get it ready for importing in to Mobile Connect.

The “secret sauce” of the code lies in the nested Replace() and Translate() functions below:

REPLACE(TRANSLATE(PersonMobilePhone, 'abcdefghijklmnopqrstuvwxyz+()- ,#+', '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@', '')

The translate function will look for our defined “illegal” characters and replace it with an “@” symbol. Finally the Replace function remove the “@” symbol leaving us with a rinsed phone number. From here, we can run additional checks to ensure the length and starting digits are what we expect. The snippet below is looking to check for AU and US phone numbers.

If this helped you, I’d love to know!

Full Code

In most “older” accounts in SFMC your SQL functions will have not been updated so you won’t not have access to the TRANSLATE() function. This this case you might encounter and error such as: TRANSLATE’ is not a recognized built-in function name.

In this case, you can replicate the functionality by using many nested REPLACE() functions — although it does make it less elegant.

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(
PersonMobilePhone,
'a', ''), 'b', ''), 'c', ''), 'd', ''), 'e', ''), 'f', ''), 'g', ''), 'h', ''), 'i', ''), 'j', ''),
'k', ''), 'l', ''), 'm', ''), 'n', ''), 'o', ''), 'p', ''), 'q', ''), 'r', ''), 's', ''), 't', ''),
'u', ''), 'v', ''), 'w', ''), 'x', ''), 'y', ''), 'z', ''), '+', ''), '(', ''), ')', ''), '-', ''), 
' ', ''), '#', ''), ',', ''), '#', '') AS mobileNumber