replace function

[vendimb15716755]

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

[Vipul_Raghav]

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

[vendimb15716755]

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

[vendimb15716755]

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

[Vipul_Raghav]

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

[Jean-Serge_Biro]

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

[vendimb15716755]

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

[Amit_Kumar]

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

[vendimb15716755]

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

[vendimb15716755]

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

[Amit_Kumar · Accepted solution]

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>

[vendimb15716755]

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

[Jean-Serge_Biro]

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

[vendimb15716755]

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"

[Jean-Serge_Biro]

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