replace function

Avatar

Avatar

vendimb15716755

Avatar

vendimb15716755

vendimb15716755

13-09-2017

Hi

I am trying to export data from recipient and another table using a workflow activity. When i export I have to build a URL which requires names to be concatenate . also when the names have space or dashes I need to remove those. 

can someone help with javascript function or any build in expression that I can use to do that?

Example would be

Jane Smith  convert to JaneSmith

John-doe convert to  johndoe

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar

Amit_Kumar

MVP

Avatar

Amit_Kumar

MVP

Amit_Kumar
MVP

17-09-2017

Hi,

Use below and it should fix your problem.

<?xml version="1.0" encoding='ISO-8859-1' ?> 

<!-- =========================================================================== 

  Additional SQL functions for Adobe Campaign 

  ========================================================================== --> 

<package 

  namespace   = "vip" 

  name        = "Vip-additional-functions" 

  label       = "Vip Additional functions" 

  buildVersion= "6.1" 

  buildNumber = "8691"> 

 

 

<entities schema="xtk:funcList"> 

<funcList name="VIPFunctionList" namespace="vip"> 

<group name="string" label="String"> 

<function name="VIPReplace" type="string" args="(&lt;String>,&lt;String>,&lt;String>)" help="Replace all occurrences in string of substring from with substring to" minArgs="3" maxArgs="3" display="Replace all occurrences in string of substring from with substring to"> 

<providerPart provider="PostgreSQL,MSSQL" body="replace($1,$2,coalesce($3,E''))"/> 

</function> 

</group> 

</funcList> 

</entities> 

</package>

Answers (13)

Answers (13)

Avatar

Avatar

Vapsy

Employee

Avatar

Vapsy

Employee

Vapsy
Employee

14-09-2017

This is because in version 6.1.1 Adobe Campaign doesn’t allow direct usage of custom SQ functions, we need to whitelist them first inside the xtk:funcList entity

Create a new option name “XtkPassUnknownSQLFunctionsToRDBMS” with type as “Integer” and value as “1”. Save it. Now execute the workflow and it should run absolutely fine. This option will enable you to bypass the SQL check that avoids injection of custom SQL function. It is a workaround.

If you wish to get your SQL function whitelisted you can contact Adobe Professional Services.

Regards,

Vipul

Avatar

Avatar

Vapsy

Employee

Avatar

Vapsy

Employee

Vapsy
Employee

13-09-2017

Hi,

There is no REPLACE function available as part of query editor in Campaign. You will have to create one of your own.

Please refer to the documentation Adding additional SQL functions

I have a package for you which you can use directly.

<?xml version="1.0" encoding='ISO-8859-1' ?>

<!-- ===========================================================================

  Additional SQL functions for Adobe Campaign

  ========================================================================== -->

<package

  namespace   = "vip"

  name        = "Vip-additional-functions"

  label       = "Vip Additional functions"

  buildVersion= "6.1"

  buildNumber = "8857">

<entities schema="xtk:funcList">

<funcList name="VIPFunctionList" namespace="vip">

<group name="string" label="String">

<function name="(example is here, you can change to the name you want : VIPReplace)" type="string" args="(&lt;LookIn&gt;,&lt;From&gt;,&lt;To&gt;)" help="Replace all occurrences in string of substring from with substring to" minArgs="3" maxArgs="3" display="Replace all occurrences in string of substring from with substring to">

<providerPart provider="PostgreSQL,MSSQL" body="replace($1,$2,$3)"/>

</function>

</group>

</funcList>

</entities>

</package>

Import it and then you will see the option inside expression editor for use.

You can then user the expression

VIPReplace(@firstName,"-","")+VIPReplace(@lastName,"-","")

Hope this helps.

Regards,
Vipul

Avatar

Avatar

Jean-Serge_Biro

MVP

Avatar

Jean-Serge_Biro

MVP

Jean-Serge_Biro
MVP

18-09-2017

Hi vendimb,

Apparently you would not have taken into account my recommandation  (my post on 14th Spe) about URL encoding and different special characters that appear frequently in people names...
So I strongly suggest you to improve the SQL function VIPreplace in order to replace these special characters as well.

Regards
J-Serge

Avatar

Avatar

vendimb15716755

Avatar

vendimb15716755

vendimb15716755

17-09-2017

Thanks Amit

that helped resolve the error. just so you know I have to do a workaround for empty space( null)

For somwhor when I do '' query converts it to null and it replace the whole string with null

the workaround I use the following ( leaving  single space and using LTRIM

VIPReplace(@firstName,'-',Ltrim(' ')) +'-' + VIPReplace(@lastName,'-',Ltrim(' '))

Thanks everyone

Avatar

Avatar

Jean-Serge_Biro

MVP

Avatar

Jean-Serge_Biro

MVP

Jean-Serge_Biro
MVP

18-09-2017

It is a Javascript function, so it can't be used in a Query activity directly.

You must develop your Replacement SQL function by coding either replacement by no space (as Amit and Vipul gave) if you intend to "normalize" the strings by deleting all special characters (replace by nothing), or replacing to equivalent ANSI characters.

For instance in French, "è" character can be either encoded (ISO encoding entity %E8) or replaced by "similar" (but not equivalent char) "e".

Same for space: encode it as nothing (suppress it) or by the ISO encoding entity %20.


So choice to make depends on your requirement and needs. Perhaps the simple replacement function given by Amit is enough for you, I was just wondering and preferring to point out it to you.

Regards
J-Serge

Avatar

Avatar

vendimb15716755

Avatar

vendimb15716755

vendimb15716755

18-09-2017

HI Jean-Serge

can this function be used on the Query task on the workflow or it has to be passed using javascript Task?

escapeUrl("Système d'exploitation") // "Syst%E8me%20d'exploitation"

Avatar

Avatar

vendimb15716755

Avatar

vendimb15716755

vendimb15716755

15-09-2017

by looking more into that here is what is doing

When there is trying to replace a - with '' it coverts that to null instead on empty and it removed the whole records. If I leave a single space it takes it but I dont want the single space. how I can get rid of that

error2.png

Avatar

Avatar

vendimb15716755

Avatar

vendimb15716755

vendimb15716755

15-09-2017

HI Amit

version 9.4 Build 8691

I also run into another issue when I import this package on build 8676. version 6.1.1

Please help....

error_postgress.png

Avatar

Avatar

Amit_Kumar

MVP

Avatar

Amit_Kumar

MVP

Amit_Kumar
MVP

14-09-2017

Hi,

Can you confirm your Postgres or MySQL version?

I have tested in my local with Postgres 9.6(and build 8857), and it's working fine for me.

Regards,

Amit

Avatar

Avatar

vendimb15716755

Avatar

vendimb15716755

vendimb15716755

14-09-2017

Thanks Vipul

that helped. for some how the function can replace the - with other characters but is not removing that if I need to concat:

example

this works :  VIPReplace(@lastName,"-","#" )

this doesnt : VIPReplace(@lastName,"-"," " )

Avatar

Avatar

Jean-Serge_Biro

MVP

Avatar

Jean-Serge_Biro

MVP

Jean-Serge_Biro
MVP

14-09-2017

Hello Vendimb,

In your use case, because it is URL generated, and there many other special characters not allowed in an URL, you should use this JSAPI function:

escapeUrl (value)

escapeUrl("Système d'exploitation") // "Syst%E8me%20d'exploitation"

So if there are diacritic glyphs in your Lastname or First Name, it will be automatically replaced by using the corresponding HTML entities.

For instance for French names, we have é è à ç ù and so forth, but also some ' glyph  (in d', L') to encode/replace.

Most of non English languages contain such symbols, especially in European languages, Spanish, Asian countries, etc.

Regards
J-Serge

Avatar

Avatar

vendimb15716755

Avatar

vendimb15716755

vendimb15716755

13-09-2017

HI Vipul. I implemented the function and when I try to call it on the expression editor ( appears there) it gives me the error below. I am admin on the system.

lVIPReplace.png

Avatar

Avatar

vendimb15716755

Avatar

vendimb15716755

vendimb15716755

13-09-2017

Thanks Vipul

I will give it a try

Also as for the javascript function is there a way of doing it? practically the data that I am manipulating will be exported to an flat file and also will be used on the delivery( will build an personalized URL within delivery content)

Thanks for your help