Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

SOLVED

Remove a special character during a data extraction

thibaultb473199
Level 4
Level 4

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
wodnicki
Correct answer by
Community Advisor
Community Advisor

Hi,

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

Thanks,

-Jon

View solution in original post

5 Replies
wodnicki
Correct answer by
Community Advisor
Community Advisor

Hi,

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

Thanks,

-Jon

View solution in original post

thibaultb473199
Level 4
Level 4

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,

wodnicki
Community Advisor
Community Advisor

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

thibaultb473199
Level 4
Level 4

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

wodnicki
Community Advisor
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