String Functions

As a rule, string functions change the input expression and return an updated string. The input expression is always regarded as a string; even if it appears as a date or number, it is interpreted as a string.

LCase Function

The LCase function assigns lower case formatting. It changes the expression to lower case characters and returns a string.

Syntax:

Lcase(expression)

Example:

Lcase("HELLO") returns the value "hello".

TCase Function

The TCase function assigns title style formatting. It changes the expression so that every new word (starting after a non-alphabetical character) will start with an upper case character and returns a string.

Syntax:

Tcase(expression)

Example:

Tcase("formatting functions") returns the value "Formatting Functions".

UCase Function

The UCase function assigns upper case formatting. It changes the expression to upper case characters and returns a string.

Syntax:

UCase(expression)

Example:

UCase("This is it") returns the value "THIS IS IT".

Length Function

The Length function returns the number of characters in the input expression.

Syntax:

Length(expression)

Example:

Length("abc") returns the value of 3

IsNullOrEmpty Function

The IsNullOrEmpty function determines if the input expression is NULL (for example, a NULL data source field) or empty (for example, an empty string). This function returns true if the string is NULL or empty and false otherwise.

Syntax:

IsNullOrEmpty(expression)

Example:

IsNullOrEmpty(|->[Address2]) returns the value of false if Address2 contains a value, and true if it is either NULL or Empty.

SubString Function

The SubString function retrieves a sub-string from expression1, with expression3 characters, starting from the position expression2.

Syntax:

SubString(expression1,expression2,expression3)

Where:

  • expression1 is always regarded as a string; even if it appears as a date or number, it will be interpreted as a string.

  • expression2 indicates the starting position of the substring.

    For example, 0 represents the first character position in expression1, 1 represents the second character position, etc.

  • expression3 is the number of characters retrieved.

For example, 3 retrieves three characters, 0 retrieves an empty string, etc.

A value of –1 indicates that all characters until the end of expression1 should be retrieved.

Examples:

SubString("abcd",1,2)   ="bc"

SubString("James",2,2)   =   "me"

SubString(1973,0,2)      =   "19"

SubString("James",2,-1)   =   "mes"

Trim Function

The Trim function trims leading and trailing white spaces in the expression.

Syntax:

Trim(expression)

Example:

Trim(" hello WORLD ") = "hello WORLD"

LTrim Function

The LTrim function trims leading white spaces in the expression.

Syntax:

LTrim(expression)

Example:

LTrim(" hello WORLD ") = "hello WORLD "

RTrim Function

The RTrim function trims trailing white spaces in the expression.

Syntax:

RTrim(expression)

Example:

RTrim(" hello WORLD ") = " hello WORLD"

Find Function

The Find function gets the character index (zero based) in expression1, where the string (expression2) is found. The search starts from the character index (expression3 – zero based). The result is -1 in case the string is not found.

Syntax:

Find(expression1, expression2, expression3)

Example:

Find("hello WORLD", "WORLD", 0) = 6

Replace Function

The Replace function replaces a part of the expression1 starting from character index (expression3 – zero based) of length (expression4) with the string (expression2).

Syntax:

Replace(expression1, expression2, expression3, expression4)

Example:

Replace("hello WORLD", "EARTH", 6, 5) = "hello EARTH"

FindAndReplace Function

The FindAndReplace function replaces all instances of the string (expression2) in expression1 with the string (expression3).

Syntax:

FindAndReplace(expression1, expression2, expression3)

Example:

FindAndReplace("hello WORLD", "WORLD", "EARTH") = "hello EARTH"

FindAndReplaceChars Function

The FindAndReplaceChars function replaces all instances of one or more characters in a string with another set of characters. For example, you can use this function to clean invalid URL characters from a string.

Syntax:

FindAndReplaceChars(expression1, expression2, expression3)

  • expression1 - a string containing the characters to be replaced

  • expression2 - list of characters to be replaced

  • expresion3 - optional. Replacement string to be used instead of any of the characters listed in expression2. The string may be empty or contain multiple characters. If the string is omitted, the characters in expression2 are removed.

  • Return value - the updated string, containing the replaced characters.

Example:

The following example replaces the space character with an underscore (“_”):

FindAndReplaceChars("John Michael Smith", " ", "_") = "John_Michael_Smith”

The following example removes the space character:

FindAndReplaceChars("John Michael Smith", " ") = "JohnMichaelSmith"

FindByRegExp

The FindByRegExp function keeps only the string matching the definition of the regular expression.  

Regular expression syntax is ECMA-262 grammar with modifications.

Syntax

FindByRegExp(exprAsString, regExpString, firstMatchOnly)

  • exprAsString - The input field to which the regular expression will be applied.

  • regExpString - The regular expression rule.

  • firstMatchOnly - If the value is False all results are returned. If True - only the first match is returned.

Example

FindByRegExp("1222 Duncan Avenue", "[A-Za-z]+",True) = Duncan

FindAndReplaceByRegExp

The FindAndReplaceByRegExp function finds patterns in text using regular expressions (RegExp) and replaces them with a specified value.

Regular expression syntax is ECMA-262 grammar with modifications.

Syntax

FindAndReplaceByRegExp(exprAsString, regExpString, replaceString, firstMatchOnly)

exprAsString - The input field to which the regular expression will be applied.

regExpString - The regular expression rule.

replaceString - The value to replace the found expression.

firstMatchOnly - If the value is False all results are replaced. If True - only the first match is replaced.

Example

FindAndReplaceByRegExp("MyFileName.Ext","\\.[^.]*$", "",False)

The function FindAndReplaceByRegExp("MyFileName.Ext","\\,[^.]*$", "", False) uses the regular expression "\\.[^.]*$" to locate a and replace (with an empty string) the file extension. In this example, the regular expression looks for the last period (\\.) and any character (not including period) that follows it ([^.]*) until the end of the string ($). If this pattern is found, it is replaced with an empty string (hence deleting the extension).

CleanNumber

The CleanNumber function allows to strip any characters from a string, leaving only digits. This is particularly useful for cleaning up telephone numbers and leaving only digits.

Syntax:

CleanNumber(exprAsString)

Example:

The following example removes the space, + and - characters from the string:

CleanNumber("+1 888-452-1111") = 18884521111

CleanRecipientKey

The CleanRecipientKey function allows you to handle invalid characters when adding new Recipient Keys to the data source (using the INSERT expression).

This function finds all instances of invalid URL characters (space, “:”, “?”, “&”, “*”, “#”, “<“, “>”, “|”, quotes,”'”) in the RecipientKey expression, and replaces them with the ReplaceInvalidWith string.

ReplaceInvalidWith is optional. If you do not specify it, the invalid URL characters in “RecipientKey” will be removed.

Syntax:

CleanRecipientKey(RecipientKey, ReplaceInvalidWith)

Example:

The following example replaces the space character with an underscore (“_”):

CleanRecipientKey("John Michael.Smith", "_") = "John_Michael.Smith"

The following example removes the space character:

CleanRecipientKey("John Michael.Smith") = "JohnMichael.Smith"

SecureID

Use the SecureID option to generate a unique, non-guessable and secure recipient ID (for example,  5adcf67b419a4ad796da4458d25a038e).

Syntax/Example

The following example generates a unique ID for each recipient:

SecureID()

HexToUnicode

Hexadecimal code allows you to represent special unicode characters, which cannot be represented by symbols, by using their hexadecimal values.
The HexToUnicode function allows you to treat a hexadecimal string as a unicode string.

The input parameter, ExprAsHexString, is a string of hexadecimal characters. Each sequence of four hexadecimal characters is converted into the matching unicode character (if the string cannot be divided into four, the function automatically pads the string with leading zeros).

Note that the input string value is ordered in big-endian.

Syntax:

HexToUnicode(ExprAsHexString)

Example:

In order to use the tab character, you can use its Hex value, 9, as follows:

"Hello" + HexToUnicode("9") + "World"

...would result in:

Hello<Tab>World

HtmlEncode

The HtmlEncode(strToEncode)function converts a string into an HTML-encoded string, and returns the encoded string. Usually used to encode a URL, so that it can be safely posted to an HTTP server (i.e. URL encoding).

Syntax

HtmlEncode(strToEncode)

Example:

HtmlEncode("http://my_domain/my page.html")

...would result in:

"http://my_domain/my%20page.html"