replace function | Community
Skip to main content
V
vendimb15716755
Level 4
September 13, 2017
Solved

replace function

  • September 13, 2017
  • 14 replies
  • 13693 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

    Show previous replies
    V
    vendimb15716755Author
    Level 4
    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

      Jean-Serge_Biro
      Jean-Serge_Biro
      Level 10
      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
        Level 4
        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
        Level 10
        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

        Terms & ConditionsAccessibility statement

        Loading footer...