Expand my Community achievements bar.

SOLVED

Remove a special character during a data extraction

Avatar

Level 5

Hello everyone,

I am running a workflow using customers data which will be extract with a data extraction (Excel) and I have some issues with the phone number data which sometimes use a "+" in the beginning of the phone number. (depending on the country)

For example : I have the use of "320123456789" and "+320123456789" for the phone number field.

My request is : is it possible to remove that "+" special character during the data extraction so this character won't be present in my Excel ? If yes, how can I make such a task ? Does this need Javascript ?

If you need more information, feel free to ask.

Thanks in advance.

Kind regards,

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi,

Iif(Charindex(@phone, '+') = 1, Substring(@phone, 2, Length(@phone)), @phone)

Thanks,

-Jon

View solution in original post

5 Replies

Avatar

Correct answer by
Community Advisor

Hi,

Iif(Charindex(@phone, '+') = 1, Substring(@phone, 2, Length(@phone)), @phone)

Thanks,

-Jon

Avatar

Level 5

Hello wodnicki​ ,

Thank you for this answer. Just to be sure, is this code needs to be put in a Javascript before the data extraction or there is a way to put that line of code in the data extraction activity itself ?

Thank you in advance.

Kind regards,

Avatar

Community Advisor

That's a sql expression, goes in the extraction activity.

Avatar

Level 5

Hello wodnicki​ ,

Thank you so much for the precision.

I have a last question : I have some phone number data (example : +0032123456789) and I want to use an "if" if this case happen and remove the "+" for this case and else it's another case (example : +32123456789), don't do anything. Is it possible to do that in SQL ? I'm not familiar with that kind of language.

Kind regards,

Thibault

Avatar

Community Advisor

Hi,

That's literally what that code says.

Campaign's expressions are an abstracted sql that are translated directly or run as db functions, for compatibility across db's.

Here 'iif' translates to 'case-when-then-else-end'.

Adobe Campaign Classic is primarily a data management tool, it would be a good investment to read up on sql and data modeling in general; or switch to a standard non-technical-manager-in-tech role .

Thanks,

-Jon