Changing incoming data in Data Loading/Update Data Function?

[alb52547777]

Hi all,

I have an SMS Gateway integrated with Adobe Campaign. The problem is it can only send SMS messages with the country code in front of the number, but most of our customer data does not contain the country code. We are in Australia (Country code +61) but mobile numbers start in 04. Thus, most of the mobile numbers in our database are stored as "04xx xxx xxx" when they need to be "614xx xxx xxx". Is there any way in a workflow or the data loading/update data function to change all entries that start with 04 to start with 614?

This is Adobe Campaign Classic v7

Many thanks,

Alex

[Garima_Gakhar · Accepted solution]

Hi alb52547777​alb52547777

There isn't an explicit 'prefix' field corresponding to the phone number which could allow you to update the prefix from 0 to 61 within a Workflow Activity.

You might have to change the phone numbers completely and that would be too inefficient since the query will have all the possible phone numbers (starting with 0) to be replaced with phone numbers starting with 61.

However you could consider the TON and NPI Settings in case the SMS Connection is being established with vendor through Extended Generic SMPP, see below Screen Shot:

NPI (Numbering Plan Identification) and TON (Type of Number) combination might allow the phone number prefix to be 0 on your side and 61 on vendor's side. For '0', TON value would be '2' (National) and for '61', TON value would be '1' (International).

The NPI value becomes '1' (ISDN/telephone numbering plan) for numbers starting with '61' and '8' for numbers starting with '0'.

Considering you are on v7, I am assuming that you might be using Extended Generic SMPP Connector only.

If so, then you could try this combination once.

For reference, below are the standard NPI and TON values:

TON:

Unknown = 0

International = 1

National = 2

Network Specific = 3

Subscriber Number = 4

Alphanumeric = 5

Abbreviated = 6

NPI:

Unknown = 0

ISDN/telephone numbering plan (E163/E164) = 1

Data numbering plan (X.121) = 3

Telex numbering plan (F.69) = 4

Land Mobile (E.212) =6

National numbering plan = 8

Private numbering plan = 9

ERMES numbering plan (ETSI DE/PS 3 01-3) = 10

Internet (IP) = 13

WAP Client Id (to be defined by WAP Forum) = 18

Hope this helps!

[preethamk581070]

Hi alb52547777​,

Alternatively, you can modify the data in transition using SQL activity like this,

UPDATE <%= vars.tableName %> SET semail = RIGHT(semail, '10');

UPDATE <%= vars.tableName %> SET semail = concat('61', semail) where length(semail) < 11 ;

Where in the first line->

semail is the column name I want to modify the data

10 - I'm keeping last 10 digits in the column, removing anything beyond 10

Second line ->

I'm adding 61 at the beginning of the each record where the record length is less than 11.

Regards,

Preetham