Remove a special character during a data extraction

Avatar

Avatar

thibaultb473199

Avatar

thibaultb473199

thibaultb473199

13-08-2019

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,

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar

wodnicki

MVP

Avatar

wodnicki

MVP

wodnicki
MVP

13-08-2019

Hi,

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

Thanks,

-Jon

Answers (4)

Answers (4)

Avatar

Avatar

wodnicki

MVP

Avatar

wodnicki

MVP

wodnicki
MVP

14-08-2019

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

Avatar

Avatar

thibaultb473199

Avatar

thibaultb473199

thibaultb473199

14-08-2019

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

Avatar

wodnicki

MVP

Avatar

wodnicki

MVP

wodnicki
MVP

13-08-2019

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

Avatar

Avatar

thibaultb473199

Avatar

thibaultb473199

thibaultb473199

13-08-2019

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,