As an Eloqua user, you will occasionally have a need to cleanse or format your data to make it usable for campaigning. In this article, we’ll cover how to use regular expression in Eloqua to format phone numbers for an SMS campaign.

This comes in handy when outputting mobile numbers and prefixing it with the country code.

To be able to do this, you require the Contact Washing Machine cloud app installed on your instance. The app is a must for anyone using Eloqua and greatly extends Eloqua’s functionality when it comes to manipulating data. You can find out about all the app action here

We’ll be using Regular Expression (Regex) within the Contact Washing Machine App to format our data.

For those who are new to Regex, I would advise you to have a quick look at https://regex101.com/ for more information and also to give you a platform to test your own scripts.

Challenge

Format 07999999999 into +447999999999

Oracle provides some guidelines on regex but you will need to get your head around it first before you can make it work for you.
The example below picks up the number in the source field and breaks it into different components that allow it to be manipulated

• Source field: +1111234567891
• Regular expression to find: ^(\\+[\\d]{1,3}|0)?(\\d{3})(\\d{3})(\\d{4})$
• Regular expression replace: ($1) $2 $3-$4
• Destination field result: (+111) 123 456-7891

Using Regular Expression in Eloqua Contact Washing Machine

The key is to break the number in the source field into bite-size chunks then write an expression to alter each of the sections.

• Source field: 07999999999
• Regular expression to find: ([\d]{1})(\d{10})$
• Regular expression replace: +44$2
• Destination field result: +447999999999

The source text has 11 characters and the goal is to split it into two sections. ([\d]{1}) isolates the first character (0) and the (\d{10}) isolates the rest of the numbers.

• ([\d]{1}) = 0
• ([\d]{10}) = 7999999999
• $1$2 = 07999999999

Final Output

Final step is to replace the $1 part with the +44

+44$2 = +447999999999