replace function

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

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

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.

l

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

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

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,"-"," " )

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

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....

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