replace function | Community
Skip to main content
V
vendimb15716755
New Participant
September 13, 2017
Solved

replace function

  • September 13, 2017
  • 14 replies
  • 13653 views

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

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by Amit_Kumar

    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>

    14 replies

    Jean-Serge_Biro
    Jean-Serge_Biro
    New Participant
    September 18, 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

    V
    vendimb15716755Author
    New Participant
    September 18, 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"

    Jean-Serge_Biro
    Jean-Serge_Biro
    New Participant
    September 18, 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

      V
      vendimb15716755Author
      New Participant
      September 18, 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

        Amit_Kumar
        Amit_KumarAccepted solution
        New Participant
        September 17, 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>
          V
          vendimb15716755Author
          New Participant
          September 15, 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

          V
          vendimb15716755Author
          New Participant
          September 15, 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....

          Amit_Kumar
          Amit_Kumar
          New Participant
          September 15, 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

          V
          vendimb15716755Author
          New Participant
          September 14, 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,"-"," " )

          Jean-Serge_Biro
          Jean-Serge_Biro
          New Participant
          September 14, 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

          Show more replies
          Terms & ConditionsAccessibility statement

          Loading footer...