Functions for Campaign

String operations

LEN

Format: LEN( string str)

Use: Returns the length of the specified string.

Parameters:
    Str (string): string from which the length must be returned

Return value:
    Integer: length of the string

Example:
Function:     LEN(@NAME) / LEN('parana')
Output:     6

 

CHARINDEX / FIND

Format: CHARINDEX( string needle, string haystack)
     FIND( string needle, string haystack)

Use: Searches haystack for the first occurrence of needle.

Parameters:
    Needle (string): string to find
    Haystack (string): string to be searched    

Return value:
    Integer: If needle found: offset of needle in haystack
        If needle not found: -1

Example:
Function:     CHARINDEX('e', @NAME)
Output:     5

Function:     CHARINDEX('e', 'Optizen')
Output:     5

 

FINDONEOF

Format: FINDONEOF( string needles, string haystack)

Use: Searches haystack for the first occurrence of any character contained in needles

Parameters:
    Needles (string): character to search for
    Haystack (string): string to be searched

Return value:
    Integer: If one of the characters from the needles is found : offset of the first character found in the haystack

        If none are found: -1

Examples:
Function:     FINDONEOF('ae', 'I love cats')
Output:     5
(starting from position 0, the e is the first character found, on position 5)

Function:     FINDONEOF(',','I love them, those cats')
Output:     11
(starting from position 0, the comma is found on position 11)

Function:     FINDONEOF('b','I love cats')
Output:     -1
(the b is not found in the haystack)

 

LASTCHARINDEX / REVERSEFIND

Format: LASTCHARINDEX( string needles, string haystack)
     REVERSEFIND( string needles, string haystack)

Use: Searches haystack for the last occurrence of needle

Parameters:
    Needles (string): character to search for
    Haystack (string): string to be searched

Return value:
    Integer: If one of the characters from needles found: last offset of character in haystack
        If none found: -1

Example:
Function:     LASTCHARINDEX('a', 'I love cats'
   REVERSEFIND('a', 'I love cats')
Output:     8

 

REPLACE

Format: REPLACE( string haystack, string originalNeedle, string newNeedle)

Use: Searches haystack for the every occurrence of originalNeedle, and replaces them with newNeedle.

Parameters:
    Haystack (string): string to be searched
    originalNeedle (string): substring that should be replaced by another one
    newNeedle (string): String that is used to replace any occurrence of originalNeedle

Return value:
    string: returns the modified haystack string

Example:
Function:     REPLACE( 'I love cats', 'cats', 'dogs')
Output:     I love dogs

 

REVERSE

Format: REVERSE( string str)

Use: Returns the reverse of a string value.

Parameters:
    str (string): string to be reversed

Return value:
    string: returns the reverse string

Example:
Function:     REVERSE('I love cats')
Output:     stac evol I

 

TRIM

Format: TRIM( string str)

Use: Trims spaces, newlines and tabs from both ends of str.

Parameters:
    str (string): string to be trimmed

Return value:
    string: returns the trimmed string

Example:
Function:     TRIM('             abc         ')
Output:     abc

 

LEFT

Format: LEFT( string str; integer count)

Use: Returns the first count characters from str.

Parameters:
    str (string): source string
    count (integer): Number of characters to select, starting from the most left character

Return value:
    string: returns the left part from str

Example:
Function:     LEFT('I love cats',5)
Output:     I lov

 

MID

Format: MID( string str; integer offset, integer count)

Use: count characters from the middle of str, starting at offset.

Parameters:
    str (string): source string
    offset (integer): starting offset
    count (integer): Number of characters to select, starting from the offset

Return value:
    string: returns the mid part from str

Example:
Function:     MID('I love cats',2,4)
Output:     love

 

Format: RIGHT( string str, integer count)

Use: Returns the last count characters from str.

Parameters:
    str (string): source string
    count (integer): Number of characters to select from the end of str

Return value:
    string: returns the right part from str

Example:
Function:     RIGHT('I love cats',4)
Output:     cats

 

UCASE

Format: UCASE( string str)

Use: Returns str, with all characters converted to uppercase.

Parameters:
    str (string): source string

Return value:
    string: returns the uppercased str

Example:
Function:     UCASE('I love cats')
Output:     I LOVE CATS

LCASE

Format: LCASE( string str)

Use: Returns str, with all characters converted to lowercase.

Parameters:
    str (string): source string

Return value:
    string: returns the lowercased str

Example:
Function:     LCASE('I love cats')Output:     i love cats

 

PCASE

Format: PCASE( string str)

Use: str, with every first character of every contained word converted to uppercase.

Parameters:
    str (string): source string

Return value:
    string: returns the converted str

Example:
Function:     PCASE('I love cats')
Output:     I Love Cats

TRANSLATE

Format: TRANSLATE( string fieldname, string multivalue, string separator1, string separator2)

Use: Translates the multivalue, will separate all values with separator1, except for the last value; that will be separated by separator2.

Parameters:
    Fieldname (string): Name of the field to which the option list is linked
    Multivalue (string): string to be translated
    Separator1 (string): Separator1    
    Separator2 (string): Separator2

Return value:
    string: returns the converted translated value

Example:
This example is for a user with a value “1,2,3,6” in the INTEREST-field on his profile
On this field the following field options are defined:
1 Sports
2 Politics
3 Movies
4 Cars
5 Animals
6 Nature

While ~$INTERESTS~ would return “Sports, Politics, Movies, Nature”, this function allows us to set the separators

Function:     TRANSLATE('INTERESTS',INTERESTS,',',' and') where INTERESTS is the name of the field in the audience list

Output:     Sports, Politics, Movies and Nature

Function:      '<UL><LI>' & TRANSLATE('INTERESTS', INTERESTS, '</LI><LI>', '</LI><LI>') & '</LI></UL>'

Output:    <UL><LI>Sports</LI><LI>Politics</LI><LI>Movies</LI><LI>Nature</LI></UL>

 

&

Format: string str1 & string str2 & string strn

Use: Concatenates the strings.

Parameters:
    Str1 (string): First string
    Str2 (string): Second string
    Strn (string): Nth string    

Return value:
    string: returns the concatenated string

Example:
Function:     'I' & ' Love ' & 'Cats'
Output:     I Love Cats

 

CONTAINSTAG

Format: CONTAINSTAG(string haystack, string needle)

Use: search for a specific string within a string of separate values.
The ‘string of values’ is split into separate items by separators (a separator can be : comma, pipe or semicolon).
The search is performed on each individual item.

Parameters:
    haystack (string): string to perform the search in
    needle (string): string to search for

Return value:
    string: returns true if the needle is found

Example:
Function:     CONTAINSTAG('CAT,DOG,Frog,doggybag,hotdog','dog')
Output:         True , as ‘dog’ is found as a value inside the string 'CAT,DOG,Frog,doggybag,hotdog'. (In this example, the string values are split by commas. Note that only ‘dog’ matches the search, as ‘doggybag’ and ‘hotdog’ are different values, and thus do not match the searched needle.)

Function:     CONTAINSTAG('CAT,doggy,bird','dog')
Output:         False , as neither ‘cat’, ‘doggy’ or ‘bird’ match the searched string ‘dog’

 

JSREPLACE

Format: JSREPLACE(string variable)

Use: Automatically escapes single quotes and removes parentheses from the variable to be used in javascript.

Parameters:
    variable (string): string to be searched    

Return value:
    string: returns the escapes/modified string

Example:
Function:     JSREPLACE(@VARIABLE)//where @VARIABLE is ab(cd'ef)gh)
Output:     abcd\'efgh

 

GETPROP (New 6.3.4)

Format:GETPROP(str, idx, sep)

Use: Automatically escapes single quotes and removes parentheses from the variable to be used in javascript.

Parameters:
    str: string containing the properties

    Idx; 1-based property index

    Sep: optional – string containing accepted separators    

Return value:
    string

Example:
Function:  GETPROP(‘Barcelona; Brussels; New York’,3,’;’))
Output:     New York

 

Math functions

ABS

Format: ABS(integer number)

Use: number, without sign. (basically converts negative numbers to positive ones).

Parameters:
    Number (integer): Number to convert

Return value:
    integer: returns the converted number

Example:
Function:     ABS(5)
Output:     5
Function:     ABS(-5)
Output:     5

 

AVG

Format: AVG(integer number_1, integer number_2, …. Integer number_n)

Use: Returns the average of the provided numbers

Parameters:
    Number_n (integer):

Return value:
    integer: returns the calculated number

Example:
Function:     AVG(1,2,3,4,5)
Output:     3

 

FMUL

Format: FMUL(float number_1, float number_2)

Use: Returns the floating point result of multiplying number_1 with number_2.

Parameters:
    Number_1,number_2 (float):numbers to multiply

Return value:
    float

Example:
Function:     FMUL(1.2,3.4)
Output:     4.08

FDIV

Format: FDIV(float nominator, float denominator)

Use: Returns the floating point result of the division of nominator with denominator.

Parameters:
    nominator (float)
    denominator (float)

Return value:
    float

Example:
Function:     FDIV(3.4, 1.2)
Output:     2.83

 

FADD

Format: FADD(float number_1, float number_2)

Use: Returns the floating point result of adding number_1 with number_2.

Parameters:
    Number_1 (float)
    Number_2 (float)

Return value:
    float

Example:
Function:     FADD(3.4, 1.2)
Output:     4.60

 

FSUB

Format: FSUB(float number_1,float number_2)

Use: Returns the floating point result of subtracting number_2 from number_1.

Parameters:
    Number_1 (float)
    Number_2 (float)

Return value:
    float

Example:
Function:     FSUB(3.4, 1.2)
Output:     2.20

 

FABS

Format: FABS(float number)

Use: converts negative float numbers to positive ones.

Parameters:
    Number (float): number to convert

Return value:
    float: the converted number

Example:
Function:     FABS(5.23)
Output:     5.23

Function:     FABS(-5.23)
Output:     5.23

 

FROUND

Format: FROUND(float number, int digits)

Use: rounds the float number to the specified number of digits.

Parameters:
    Number (float): number to convert

    Digits (int): number of digits

Return value:
    string: returns the rounded number

Example:
Function:     FROUND(1.23456,3)
Output:     1.235

Function:     FROUND(1.23456, 1)
Output:     1.2

 

FCOMPARE

Format: FCOMPARE(float number1, float number2)

Use: compares two float numbers and returns a number indicating if they are equal or not.

Parameters:
    Number1 (float): number to evaluate

    Number2 (float): number to compare with

Return value:
    Int: returns 0 is the numbers are equal, 1 if Number 1 is bigger than Number2 and -1 if Number1 is smaller than Number 2

Example:
Function:     FCOMPARE(1.23,3.456)
Output:     -1

Function:     FCOMPARE(1.23, 0-3.456)
Output:     1

Function:     FCOMPARE(4.563,1.23)
Output:     1

Function:     FCOMPARE(1.23,1.23)
Output:     0

 

% (MODULO)

Format: int number_1 % int number_2

Use: calculates the remainder on a division of number_1 by number_2.

Parameters:
    Number_1 (integer): divided  number

    Number_2 (integer): Divider

Return value:
    int: the remaining number

Example:
Function:     3%2
Output:     1

Function:     12%8
Output:     4

 

FORMATNUMBER

Format: FORMATNUMBER(float value, int decimals, string thousandSeparator, string decimalSeparator, string prefix, string postfix)

Use: Formats a given number with custom separators, prefix and postfix.

Parameters:

Value  (float): The number to format
Decimals  (int): The number of decimals to show
ThousandSeparator (string: The character to show in between thousands-groups
DecimalSeparator  (string: The character to show between the whole- and the decimal-part
Prefix  (string): The text to show in front of the number
Postfix (string): The text to show at the end of the number

Return value:
    string: The formatted number

Example:
Function:     FORMATNUMBER(1234567.1234, 2, '.', ',', '€ ', ' ,-')
Output:     € 1.234.567,12 ,-

Function:     FORMATNUMBER(1234567.1234, 3, ',', '.', '$ ', '##')
Output:     $ 1,234,567,123##

Function:    FORMATNUMBER(123.456, 2, ',', '.', '¥', '')
Output:    ¥ 123.46

 

Date functions

DATEADD

Format: DATEADD(constant datetype, integer amount, datetime date)

Use: Returns datetime, added with an amount of years, months, days,… depending on the datetype. If the function is used with negative numbers, use singles quotes around the number.

Parameters:
    datetype (constant)

yyyy or yy ( = years)
mm or m ( = months)
wk or wks ( = weeks)
dd or d ( = days)
hh or h ( = hours)
mi or n ( = minutes)
ss or s ( = seconds)

amount (integer): number of units of type datetype to add
date (datetime): date to add to

Return value:
    Datetime: Returns the resulting datetime value

Example:
Function:     DATEADD('dd',4, '2009-09-24')
Output:     2009-09-28 00:00:00

Function with negative number: DATEADD('dd','-1',GetDate())

 

DATEPART

Format: DATEPART(constant datetype, datetime date)

Use: Returns the part of the date, indicated by datetype

Parameters:
    datetype (constant):

yyyy or yy ( = years)
mm or m ( = months)
wk or wks ( = sunday based weeks)
wkm (= monday based weeks)
dd or d ( = days)
dw ( = day of week)
hh or h ( = hours)
mi or n ( = minutes)
ss or s ( = seconds)

    date (datetime): date to get the part from

Return value:
    Integer: Returns the requested part of the date, indicated by datetype

Example:
Function:     DATEPART('yyyy', '2009-09-24')
Output:     2009

Function:     DATEPART('mm', '2009-09-24')
Output:     9

 

DATEDIFF

Format: DATEDIFF(constant datetype, datetime date_1,datetime date_2)

Use: Returns the difference between the part of date_1 and date_2, indicated by datetype.

Parameters:
    datetype (constant):

yyyy or yy ( = years)
mm or m ( = months)
dd or d ( = days)
hh or h ( = hours)
mi or n ( = minutes)
ss or s ( = seconds)

    Date_1 (datetime)
    Date_2 (datetime)

Return value:
    Integer: Returns the difference between the requested part of date_1 and the part of date_2

Example:
Function:     DATEDIFF('mm', '2009-01-12','2009-09-24')
Output:     8

 

GETDATE

Format: GETDATE()

Use: Returns the current local system date and time.

Parameters:
    none

Return value:
    datetime: Returns the current local system date and time

Example: On September 24th 2009 at 1PM:
Function:    GETDATE()
Output:     2009-09-24 13:00:00

 

GETUTCDATE

Format: GETUTCDATE()

Use: Returns the current UTC date and time.

Parameters:
    none

Return value:
    datetime: Returns the current UTC date and time

Example: On October 11th 2021 at 2PM:
Function:    GETUTCDATE()
Output:     2021-10-11 14:00:00

 

ISDATE

Format: isdate (string dt)

Use: Returns 1 (=true) if the specified string dt can be converted into a date(/time), otherwise returns 0 (=false)

Parameters:
    dt (string): date(/time) to be checked

Return value:
    boolean: If date(/time) appears to be valid, the return value is 1, otherwise 0

Examples:
Function:     isdate('01-05-2023')
Output:     1 (=true)

Function:     isdate('2028-02-31')
Output:     0 (=false)

Function:     isdate('2023-02-28 10:00')
Output:     1 (=true)

Function:     isdate('2023-11-10 10h00')
Output:     0 (=false)

 

FORMATDATETIME

Format: FORMATDATETIME(datetime date, constant format, string locale)

Use: Returns a string representation of the specified date in the supplied format.

Parameters:
    Date (datetime): date to convert to string

    Format (constant):

        %a:     Abbreviated weekday name
        %A:     Full weekday name
        %b:     Abbreviated month name
        %B:     Full month name
        %c:     Date and time representation appropriate for locale
        %d:     Day of month as decimal number (01 – 31)
        %H:     Hour in 24-hour format (00 – 23)
        %I:     Hour in 12-hour format (01 – 12)
        %j:     Day of year as decimal number (001 – 366)
        %m:     Month as decimal number (01 – 12)
        %M:     Minute as decimal number (00 – 59)
        % p:     Current locale's A.M./P.M. indicator for 12-hour clock
        %S:     Second as decimal number (00 – 59)
        %U:     Week of year as decimal number, with Sunday as first day of week
               (00 – 53)
        %w:     Weekday as decimal number (0 – 6; Sunday is 0)
        %W:     Week of year as decimal number, with Monday as first day of week
               (00 – 53)
        %x:     Date representation for current locale
        %X:     Time representation for current locale
        %y:     Year without century, as decimal number (00 – 99)
        %Y:     Year with century, as decimal number
        %z,%Z: Time-zone name or abbreviation; no characters if time zone is unknown
        %%:     Percent sign

    Locale (string) – optional parameter: converts the datetime to the given locale

Return value:
    string: Returns the formatted string

Example: On May 6th 2021 at 11:13 AM:

Function :
FORMATDATETIME(GETDATE(), '%A, %B %d, %Y')
Output:     Thursday, May 6, 2021

Function :
FORMATDATETIME(GETDATE(),'%c')
Output:    Thu May 6 11:13:39 2021

Function :
FORMATDATETIME(GETDATE(),'%j')
Output:     126

Function – using the optional ‘locale’ parameter :
FORMATDATETIME(GETDATE(), '%A, %B %d, %Y', 'fr-FR')

Output:     jeudi, mai 06, 2021

Function – using the optional ‘locale’ parameter :
FORMATDATETIME(GETDATE(),'%c', 'nl-BE')

Output:     6/05/2021 11:13:39

 

YEAR

Format: YEAR( string date)

Use: Returns the year of the specified datetime.

Parameters:
    Date (datetime):

Return value:
    integer:

Example:
Function:     YEAR('2009-01-12 12:34:56')
Output:     2009

 

MONTH

Format: MONTH( string date)

Use: Returns the month of the specified datetime.

Parameters:
    Date (datetime):

Return value:
    integer:

Example:
Function:     MONTH('2009-01-12 12:34:56')
Output:     1

DAY

Format: DAY( string date)

Use: Returns the day of the specified datetime.

Parameters:
    Date (datetime):

Return value:
    integer:

Example:
Function:     DAY('2009-01-12 12:34:56')
Output:     12

 

HOUR

Format: HOUR( string date)

Use: Returns the hour of the specified datetime.

Parameters:
    Date (datetime):

Return value:
    integer:

Example:
Function:     HOUR('2009-01-12 12:34:56')
Output:     12

 

MINUTE

Format: MINUTE( string date)

Use: Returns the hour of the specified datetime.

Parameters:
    Date (datetime):

Return value:
    integer:

Example:
Function:     MINUTE('2009-01-12 12:34:56')
Output:     34

 

SECOND

Format: SECOND( string date)

Use: Returns the second of the specified datetime.

Parameters:
    Date (datetime):

Return value:
    integer:

Example:
Function:     SECOND('2009-01-12 12:34:56')
Output:     56

 

DAYOFWEEK

Format: DAYOFWEEK( string date)

Use: Returns the day of the week of the specified datetime, with Sunday as first day of week.

Parameters:
    Date (datetime):

Return value:
    integer:

Example:
Function:     DAYOFWEEK('2009-01-12 12:34:56')
Output:     2

 

GETCURRENTUNIXTIMESTAMP()

Format: GETCURRENTUNIXTIMESTAMP()

Use: retrieves the unix timestamp in milliseconds

Parameters: none

Return value:  datetime

 

TOUNIXTIMESTAMP

Format: TOUNIXTIMESTAMP(date)

Use: converts a date to UNIX timestamp in seconds

Parameters: date (datetime): date to be converted

Return value:  datetime: returns a UNIX timestamp

 

Other functions

CHKPROP

Format: CHKPROP( string multivalue, string value)

Use: Searches a Campaign multivalue (pipe or comma separated string) for the occurrence of value.

Parameters:
    multivalue (string): string to search
    value (string); string to find

Return value:
    Boolean: If value found; returns 1, otherwise 0

Example:
Function:     CHKPROP('a,b,c,d,e','b')
Output:     1

Function:     CHKPROP('a,b,c,d,e','z')
Output:     0

 

PROPCOUNT

Format: PROPCOUNT( string multivalue)

Use: the amount of values, contained in the Campaign multivalue.

Parameters:
    multivalue (string): string to search

Return value:
    integer: The amount of contained values

Example:
Function:     PROPCOUNT('a,b,c,d,e')
Output:     5

 

MATCHCOUNT

Format: MATCHCOUNT( string multivalue_1, string multivalue_2)

Use: Counts the amount of values that match between the 2 Campaign multivalues.

Parameters:
    Multivalue_1 (string)
    Multivalue_2 (string)

Return value:
    integer: The amount of matched values found

Example:
Function:     MATCHCOUNT('a,b,c,d,e','b,d,e,f,g')
Output:     3

 

MAKELEN

Format: MAKELEN( int number, int desiredlen)

Use: If the amount of decimals in number is lower than desiredlen, the number is prefixed with '0's, until the length matches with desiredlen.

Parameters:
    number (integer): number
    desiredlen (integer): string to find

Return value:
    integer: number decreased or completed to the desired length

Example: For user 1 in the list:
Function:     MAKELEN(ID,5)
Output:     00001

 

IF

Format: IF(Boolean constraint, variant ifTrue, variant ifFalse)

Use: ifTrue if constraint evaluates to true, otherwise returns ifFalse.

Parameters:
    constraint (boolean): Constraint to evaluate. Can be a combination or nesting of every     supported Campaign function
    ifTrue (variant): Return value if constraint evaluates to true
    ifFalse (variant): Return value if constraint evaluates to false

Return value:
    variant: ifTrue or ifFalse, depending of the evaluation of constraint

Example:
Function:     IF('A'='A','Identical', 'Different')
Output:     Identical

Function:     IF('A'='B','Identical', 'Different')
Output:     Different

 

URLENCODE

Format: URLENCODE(string value)

Use: Returns the urlencoded version of value.

Parameters:
    value (string): value to convert

Return value:
    string: Urlencoded version of value

Example:
Function:     URLENCODE('info@parana.com')
Output:     info%40parana%2Ecom

 

URLDECODE

Format: URLDECODE(string encodedvalue)

Use: Returns the urldecoded version of value.

Parameters:
    encodedvalue (string): value to convert

Return value:
    string: Urldecoded version of encodedvalue

Example:
Function:     URLDECODE('info%40parana%2Ecom')
Output:     info@parana.com

 

H2U

Format: H2U(string str)

Use: Converts the html encoded values contained in str to a Unicode string (this function only performs a conversion  on a Unicode installation; for ansi installations, the returnvalue is identical to the supplied parameter, without doing a conversion)..

Parameters:
    str (string): String containing html encoded values (&# + ASCII-value) to convert to unicode

Return value:
    String: converted str

Example:
Function:     H2U('&#64;')
Output:     @

 

H2A

Format: H2A(string str)

Use: Converts the html encoded values contained in str to an Ansi string (this function works on both Ansi and Unicode installations, but will only perform a conversion  of Htmlencoded values within the ansi range (0-0xff) ).

Parameters:
    str (string): String containing html encoded values to convert to ansi

Return value:
    String: converted str

Example:
Function:     H2A('&#64;')
Output:     @

 

U2H (only available in Unicode version of Campaign)

Format: U2H(string str)

Use: Converts the Unicode characters in str that are out of the standard range ( >0x7F) to their html encoded values. (this function only performs a conversion  on a Unicode installation; for ansi installations, the return value is identical to the supplied parameter, without doing a conversion).

Parameters:
    str (string): String containing unicode characters that are out of the standard range

Return value:
    String: converted str

Example:
Function:     U2H('@')
Output:     @ (character is in standard range, hence no transFormation)

Function:     U2H('ö')
Output:     &#246;

A2H (only available in Unicode version of Campaign)

Format: A2H(string str)

Use: Converts the ansi characters in str that are out of the standard range ( >0x7F) to their html encoded values.  (this function works on both Ansi and Unicode installations, but will only perform a conversion  of values within the ansi range (0-0xff) ).

Parameters:
    str (string): String containing ansi characters that are out of the standard range

Return value:
    String: converted str

Example:
Function:     A2H('@')
Output:     @ (character is in standard range, hence no transformation)

Function:     A2H('ö')
Output:     &#246;

 

CHKMAIL

Format: CHKMAIL( string emailaddress [, integer timeout])

Use: Performs a basic syntax check of the supplied email address

If the optional parameter timeout has been specified, the function will perform a physical check of the emailaddress on the appropriate domain

Parameters:
    Emailaddress (string): Email address to check
    timeout (integer): Optional; unit is in milliseconds

Return value:
    boolean: If emailaddress is valid, the return value is 1, otherwise 0

Example:
Function: CHKMAIL('info@parana.com')
Output: 1

Function: CHKMAIL('infoparana.com')
Output: 0
Function: CHKMAIL('info@parana.com',3000)
Output: 1

ISNUMERIC

Format: ISNUMERIC( variant param)

Use: Checks if param is (or can be converted to) a valid numeric value.

Parameters:
    param (variant    

Return value:
    boolean: If param appears to be numeric, the Return value is 1, otherwise 0

Example:
Function:     ISNUMERIC('abc')
Output:     0

Function:     ISNUMERIC('123')
Output:     1

Function:     ISNUMERIC('123abc')
Output:     0

CONVERTPHONENUMBER

Format: CONVERTPHONENUMBER( string phone_nr, integer country_dial_prefix,

    integer maxshortcodelength).

Use: Normalizes a phone number to the international format if possible.

    If the phone number doesn't contain the country dial prefix, the provided     country_dial_prefix parameter will be used.

When the maxshortcodelength parameter contains a greater value than the length of the phone number than the number is untouched.

When only 2 Parameters are used (no maxshortcodelength parameter) or when more than 3 parameters are used (which does not have any added value), the maxshortcodelength defaults to value 4.

Parameters:
    normalized    

Return value:

    string: Converted phone number

Example:
Function:     CONVERTPHONENUMBER('0477 112345',32,11)
Output:     0477112345

Untouched, as the maxshortcodelength is greater than the length of the phone number

Function:     CONVERTPHONENUMBER('+32 477 112345',32,4)
Output:     +32477112345

Function:     CONVERTPHONENUMBER('0032 477 112345',32,4)
Output:     +32477112345

Function:     CONVERTPHONENUMBER('0477 112345',32,4)
Output:     +32477112345

Function:     CONVERTPHONENUMBER('0477 112345',32,11,0,0,0)
Output:     +32477112345

Formatted, as the maxshortcodelength defaults to value 4

 

UTF8

Format: UTF8( string value )

Use: Converts the supplied value to UTF8.

Parameters:
    Value (string): string to convert to UTF8    

Return value:
    string: Converted value

Example:
Function:     UTF8('Bàrt')
Output:     bà rt

 

UTF8DECODE

Format: UTF8DECODE( string value, string charset )

Use: Converts the supplied UTF8-value to the provided charset.

Parameters:
    Value (string): UTF8-String to convert to UTF8
    charset (string): the charset to convert the value to

Return value:
    string: Converted value

Example:
Function:     UTF8DECODE(UTF8('bàéèrt'),'iso-8859-1')
Output:     bàéèrt

 

CONVERTCHARSET

Format: CONVERTCHARSET( string value, string charset1, string charset2 )

Use: Converts the supplied value from the provided charset to the second provided charset.

Parameters:
    Value (string): UTF8-String to convert to UTF8
    charset1 (string): the charset to convert the value to

Return value:
    string: Converted value

Example:
Function:     CONVERTCHARSET('bàrt','iso-8859-1','utf-8')
Output:     bà rt

 

MDENCODE

Format: MDENCODE( string value )

Use: Returns the MD5-encoded value of the provided value.

Parameters:
    Value (string): String to be encoded

Return value:
    string: Converted value

Example:
Function:     MDENCODE('abcdefgh')
Output:     E8DC4081B13434B45189A720B77B6818

 

MDENCODE_W

Format: MDENCODE_W( string value )

Use: Returns the MD5-encoded value after a conversion to UTF-8 of the provided value

Parameters:

    Value (string): string to be encoded

Return value:

    String: encoded value

Example:
Function: MDENCODE_W(MAIL) where MAIL=Ben.demoree@parana.com

Output:     3000DA4541BEAD6FFB5E548A425DE7A2

 

GETCAMPAIGNID

Format: GETCAMPAIGNID(string hashcode)

Use: Returns the ID of the campaign the component is in.

Parameters:
    hashcode (string): hashcode to decode the ID from

Return value:
    string: Current CampaignID

Example:
Function:     GETCAMPAIGNID(@ID)
Output:     1

 

GETACTIONID

Format: GETACTIONID(string hashcode)

Use: Returns the ID of the active component in the current campaign.

Parameters:
    hashcode (string): hashcode to decode the ID from

Return value:
    string: Current ActionID

Example:
Function:     GETACTIONID(@ID)
Output:     4

GETLISTID

Format: GETLISTID(string hashcode)

Use: Returns the ID of the list the current campaign is working on.

Parameters:
    hashcode (string): hashcode to decode the ID from

Return value:
    string: Current ListID

Example:
Function:     GETLISTID(@ID)
Output:     12

 

GETUSERID

Format: GETUSERID(string hashcode)

Use: Returns the ID of the current user.

Parameters:
    hashcode (string): hashcode to decode the ID from

Return value:
    string: Current UserID

Example:
Function:     GETUSERID(@ID)
Output:     27

 

GETPROBEID

Format: GETPROBEID(string hashcode)

Use: Returns the ID of the clicked sensor.

Parameters:
    hashcode (string): hashcode to decode the ID from

Return value:
    string: Current ProbeID

Example:
Function:     GETPROBEID(@ID)
Output:     100

GETTRIGGERID

Format: GETTRIGGERID(string hashcode)

Use: Returns the ID of the trigger that initiated the execution of the campaign-flow.

Parameters:
    hashcode (string): hashcode to decode the ID from

Return value:
    string: Current TriggerID

Example:
Function:     GETTRIGGERID(@ID)
Output:     0

 

GETCATALOGLISTID

Format: GETCATALOGLISTID(string hashcode)

Use: Returns the ID of the article list that contained the link that initiated the current step.

Parameters:
    hashcode (string): hashcode to decode the ID from

Return value:
    string: Current CatalogListID

Example:
Function:     GETCATALOGLISTID(@ID)
Output:     344

 

GETCATALOGITEMID

Format: GETCATALOGITEMID(string hashcode)

Use: Returns the ID of the article that contained the link that initiated the current step.

Parameters:
    hashcode (string): hashcode to decode the ID from

Return value:
    string: Current CatalogItemID

Example:
Function:     GETCATALOGITEMID(@ID)
Output:     17

CHKBANKACCOUNT

Format: CHKBANKACCOUNT( string accountnumber, string countrycode )

Use: Checks the format of an account number.

BE

Checks if the length of the accountnumber is 12, performs a modulo-check (%97) and checks the checksum-digits.

NL

Checks if the length is less than 11.
In case the length is less than 8, the accountnumber is accepted as 'GIRO' number.

If the length  equals 9 or 10, a modulo-check (%11) is performed and checked with the checksum-digits.

Parameters:
    Accountnumber (string): the account number to be checked
    Countrycode (string): the ISO- country code. Possible values are BE and NL

Return value:
    integer:

Example:
Function:     CHKBANKACCOUNT('1234', 'NL')
Output:     1 (valid - Giro)

Function:     CHKBANKACCOUNT('12345678912', 'NL')
Output:     0 (invalid)

Function:     CHKBANKACCOUNT('000-0000011-11', 'BE')
Output:     1 (valid )

 

CHKIBAN (from v6.3.4)

Format: CHKBIBAN( string accountnumber)

Use: Checks if an account number is a valid IBAN number

Parameters:
    Accountnumber (string): the account number to be checked

Return value:
    1: valid or 0: not valid

Example:
Function:     CHKBIBAN('BE6853900754034')
Output:     0

 

GENERATEPWD

Format: GENERATEPWD(int length )

Use: Returns a strong password of the desired length.
Minimum length is 7; if a length smaller than 7 is specified, the function will default to 7.

Parameters:
    length (int): the length of the password to generate

Return value:
    integer: indicating true or false

Example:
Function:     GENERATEPWD(1)
Output:     qB1Ps!K

Function:     GENERATEPWD(10)
Output:     4hNNMD?t9g

 

CHKREGEX

Format: CHKREGEX (string RegularExpression , string fieldname )

Use: Validates a value with Regular Expression.

Parameters:
    RegularExpression (string): the regular expression to compare with

    Fieldname (string): the variable to be validated

Return value:
    integer: true or false

Example:
Function:     CHKREGEX('^([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@(([0-9a-zA-Z])+([-\w]*[0-9a-zA-Z])*\.)+[a-zA-Z]{2,9})$', MAIL)
Output:     1 (valid)

Function:     CHKREGEX('^([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@(([0-9a-zA-Z])+([-\w]*[0-9a-zA-Z])*\.)+[a-zA-Z]{2,9})$', @MAIL)
Output:     1 (valid)

Function:     CHKREGEX('^([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@(([0-9a-zA-Z])+([-\w]*[0-9a-zA-Z])*\.)+[a-zA-Z]{2,9})$', NAME)
Output:     0 (invalid)

 

GENERATEGUID

Format: GENERATEGUID( )

Use: Validates a unique string.

Parameters:
    None

Return value:
    string: Unique ID

Example:
Function:     GENERATEGUID()
Output:     956e45d6443c4e3617ee64

 

WORDWRAP

Format: WORDWRAP(int LineLength , string Text , int TabLength)

Use: returns wrapped text.

Parameters:
    LineLength: the maximum length of a rule of text

    Text: the text that needs to be wrapped

    TabLength: number of spaces a tab takes

Return value:
    string: wrapped text

Note: this function only works correctly in the text version of the message.

 

STRIPHTML

Format: STRIPHTML(string HTML  , string IncludeTags , string ExcludeTags)

Use: returns an html in which some tags have been removed. Typically, this function replaces BR, P, tags by a linefeed or LI tags are replaced by lists.

Parameters:
    Html: the html to parse

    IncludeTags: the list of tags to strip/remove. If multiple tags they are pipe separated

    ExcludeTags: the list of tags that cannot be stripped/removed. If multiple tags they are pipe separated.

Return value:
    string: stripped HTML

Function: STRIPHTML('<p>Hello <strong>world</strong></p><p>This is an example</p>')
Output:
Hello world
This is an example

Function: STRIPHTML('<p>Hello <strong>world</strong></p><p>This is an example</p>', '', 'strong')
Output:
Hello <strong>world</strong>
This is an example

Function: STRIPHTML('<ul><li>item 1</li><li>item 2</li><li>item 3</li></ul>')
Output:
• item 1
• item 2
• item 3

 

SHAENCODE

Format: SHAENCODE(SHAVersion, toEncode)

Use: encodes a string in SHA-version number

Parameters:

SHAVersion: the encoding to be applied. Possible options are 1, 224, 256, 384 and 512. Any other version will result in an empty string

toEncode: the string to encode

Return value:  string: encoded string

 

BASE64ENCODE

Format: BASE64ENCODE(toEncode)

Use: encodes a string in BASE64

Parameters:

toEncode: the string to encode

Return value:  string: encoded string

 

BASE64DECODE

Format: BASE64DECODE(toDecode)

Use: decodes a BASE64 to a string

Parameters:

toDecode: the encoded string to decode

Return value:  string: decoded string

 

TOBOOL

Format: TOBOOL( var).

Use: converts the value to a boolean (1 or 0).

Parameters:
    var to convert    

Return value:

    A boolean

 

TOINT

Format: TOINT( var).

Use: converts the value to an integer

Parameters:
    var to convert    

Return value:

    An integer

 

TOFLOAT

Format: TOFLOAT( var).

Use: converts the value to a float

Parameters:
    var to convert    

Return value:

    A float

 

TODATE

Format: TODATE( var).

Use: converts the value to a date.

Parameters:
    var to convert    

Return value:

    A date

 

TODATETIME

Format: TODATETIME( ar).

Use: converts the value to a datetime.

Parameters:
    var to convert    

Return value:

    A datetime

 

TOSTRING

Format: TOSTRING(var).

Use: converts the value to a string

Parameters:
    var to convert    

Return value:

    A string

 

LOCALIZE

Format: LOCALIZE(var,locale)

Use: Use this function to localize a field (eg. Date)

Parameters:

Var representing the field to localize

Local: the local to which the variable should be localized

Return value: depends on the settings of the sytem.

 

FORMATSQLLISTELEMENTS

Format: FORMATSQLLISTELEMENTS (parameter1, parameter2)

Use: Given a parameter (parameter1) that consists of a list of values separated by a supported separator (see below), the function will securely format that list of values, mainly for the use within an sql context (especially within an IN() statement).

Parameters:

parameter1 (mandatory) : a parameter that results in a list of elements, separated by a supported separator ( '|' or ';' or ',' )

parameter2 (optional, but highly recommended) : the separator that’s used in parameter1 (if not specified, it is automatically determined based on the list above – which is less performant and more error prone, if the content would contain one of the above separators as part of its content)

Return value:

If parameter2 is specified and valid, or if a matching separator has been detected, the function will properly format and escape every element for safe usage within a sql expression.

If parameter2 is not specified, and NO match with a separator can be found in parameter1, then parameter1 is treated as a single list element and escaped as such.

During formatting, the function will prevent re-escaping already escaped content.
    

Examples:
Function:     FORMATSQLLISTELEMENTS(@IDS, ',')
where @IDS = '123','456'
Output:     List of values (123,456) that can be safely used within sql expressions.

Function:     FORMATSQLLISTELEMENTS(@IDS, ',')
where @IDS = 123,456
Output:     List of values (123,456) that can be safely used within sql expressions.

 

Template functions

COUNT/ARTICLE_COUNT

Format:     COUNT( string ArticleContainerName [ \ string FilterName ] )
    ARTICLE_COUNT( string ArticleContainerName [ \ string FilterName ] )

Use: Returns the number of articles in the article-container or article-container-list.

Parameters:
    ArticleContainerName (string): name of the article container
    FilterName (string): Name of the filter on the article container

Return value:
    string: the number of articles in the container

Example:
Function:     COUNT('ARTICLES')
COUNT('ARTICLES\TOC')
Output:     5

 

ARTICLE_PROPERTY

Format:     ARTICLE_PROPERTY( string ArticleContainerName [ \ string FilterName],
int ArticleIndex, string FieldName )

Use: Returns the content of the FieldName from a specific article (ArticleIndex) in a specific article container (ArticleContainerName).

Parameters:
    ArticleContainerName (string): name of the article container
    FilterName (string): Name of the filter on the article container
    ArticleIndex (int): ID of the article container
    Fieldname (string): Name of the field to get the content from

Return value:
    string: the content retrieved from the article field

Example:
Function:     ARTICLE_PROPERTY('ARTICLES\TOC',1, 'Title')
Output:     I love cats

 

CONVERT

Format: CONVERT ('datetime', string value, int format )

Use: Converts provided value to type datetime

Parameters:
    value (string): string to be converted to date
    format (integer): format to convert the date to. This value will always be 120

Return value:
    string: returns the converted datetime

Example:
Function:     CONVERT('datetime', '2009-06-13', 120 )
Output:     2009-06-13 00:00:00

 

Addendum

Grid support

The Campaign V6 functions in this document are supported on Grid, except the following ones :

JSREPLACE

PROPCOUNT

U2H

A2H

CHKMAIL

UTF8DECODE

CONVERTCHARSET

GETCAMPAIGNID

GETACTIONID

GETLISTID

GETUSERID

GETPROBEID

GETTRIGGERID

GETCATALOGLISTID

GETCATALOGITEMID

CHKBANKACCOUNT

CHKIBAN

GENERATEPWD

CHKREGEX

BASE64DECODE

 

Not supported functions in emails (MTA & Grid)

The following Campaign V6 functions are not supported in emails (MTA & Grid).

ISNULL

This function has specifically been designed to support the Data component in Campaign journeys.

NULL

This function has specifically been designed to support the Data component in Campaignjourneys.