Documentation Campaign Campaign Standard Documentation

List of functions

Last update: September 21, 2021

Applies to:
Campaign Standard

Topics:
Workflows

CREATED FOR:

Experienced
Developer

About functions

The query editing tool allows you to use advanced functions to carry out complex filtering. To do this, the tool palette contains the Expression element that you can use in the workspace. Further information on this element is detailed in a specific section.

This element allows you to enter your conditions manually. Here you can use the functions defined in the following sections.

Several function types are available, depending on the desired results and the types of manipulated data:

Dates
Geomarketing
Numerical values
Other functions
Aggregates
String manipulation
Sorting

NOTE

Additional functions are available in all the activities that allow you to use events variables after calling a workflow with external parameters. They are detailed in this section.

Dates

The date functions are used to manipulate date or time values.

Name	Description	Syntax
AddDays	Adds a number of days to a date	AddDays(<date>, <number>)
AddHours	Adds a number of hours to a date	AddHours(<date>, <number>)
AddMinutes	Adds a number of minutes to a date	AddMinutes(<date>, <number>)
AddMonths	Adds a number of months to a date	AddMonths(<date>, <number>)
AddSeconds	Adds a number of seconds to a date	AddSeconds(<date>, <number>)
AddYears	Adds a number of years to a date	AddYears(<date>, <number>)
DateOnly	Returns the date only (with time at 00:00)	DateOnly(<date>)
Day	Returns the number representing the day of the date	Day(<date>)
DayOfYear	Returns a number representing the day in the year of the date	DayOfYear(<date>)
DaysAgo	Returns the current date minus n days	DaysAgo(<number>)
DaysAgoInt	Returns the current date minus n days (as an integer yyyymmdd)	DaysAgoInt(<number>)
DaysDiff	Number of days between two dates	DaysDiff(<end date>, <start date>)
DaysOld	Returns the age in days of a date	DaysOld(<date>)
GetDate	Returns the current system date of the server	GetDate()
Hour	Returns the hour of the date	Hour(<date>)
HoursDiff	Returns the number of hours between two dates	HoursDiff(<end date>, <start date>)
LocalToUTC	Converts a local date and time to UTC	LocalToUTC(<date>, <Time Zone>)
Minute	Returns the minutes of the date	Minute(<date>)
MinutesDiff	Returns the number of minutes between two dates	MinutesDiff(<end date>, <start date>)
Month	Returns the number representing the month of the date	Month(<date>)
MonthsAgo	Returns the date corresponding to the current date minus n months	MonthsAgo(<number>)
MonthsDiff	Returns the number of months between two dates	MonthsDiff(<end date>, <start date>)
MonthsOld	Returns the age in months of a date	MonthsOld(<date>)
Second	Returns the seconds of the date	Second(<date>)
Oldest	Returns the oldest date	Oldest(<Date>, <Date>)
SecondsDiff	Returns the number of seconds between two dates	SecondsDiff(<end date>, <start date>)
SubDays	Subtracts a number of days from a date	SubDays(<date>, <number>)
SubHours	Subtracts a number of hours from a date	SubHours(<date>, <number>)
SubMinutes	Subtracts a number of minutes from a date	SubMinutes(<date>, <number>)
SubMonths	Subtracts a number of months from a date	SubMonths(<date>, <number>)
SubSeconds	Subtracts a number of seconds from a date	SubSeconds(<date>, <number>)
SubYears	Subtracts a number of years from a date	SubYears(<date>, <number>)
ToDate	Converts a date + time as a date	ToDate(<date + time>)
ToDateTime	Converts a string to a date + time	ToDateTime(<string>)
ToDateTimeWithTimezone	Converts a string to a date + timezone. Example: ToDateTimeWithTimezone ("2019-02-19 08:09:00", "Asia/Tehran")	ToDateTimeWithTimezone(<string>)
TruncDate	Rounds a date+time to the nearest second	TruncDate(@lastModified, <number of seconds>)
TruncDateTZ	Rounds a date + time to a given precision expressed in seconds	TruncDateTZ(<date>, <number of seconds>, <time zone>)
TruncQuarter	Rounds a date off to the quarter	TruncQuarter(<date>)
TruncTime	Rounds the time part up to the nearest second	TruncTime(<date>, <number of seconds>)
TruncWeek	Rounds a date off to the week	TruncWeek(<date>)
TruncYear	Rounds a date + time to January 1st of the year	TruncYear(<date>)
WeekDay	Returns the number representing the day in the week of the date	WeekDay(<date>)
Year	Returns the number representing the year of the date	Year(<date>)
YearAnd Month	Returns the number representing the year and month of the date	YearAndMonth(<date>)
YearsDiff	Returns the number of years between the two dates	YearsDiff(<end date>, <start date>)
YearsOld	Returns the age in years of a date	YearsOld(<date>)

Geomarketing

The geomarketing functions are used to manipulate geographical values.

Name	Description	Syntax
Distance	Returns the distance in kilometers between two points defined by their longitude and latitude (expressed in degrees)	Distance(<Longitude A>, <Latitude A>, <Longitude B>, <Latitude B>)

Numerical

The numerical value functions are used to convert text to numbers.

Name	Description	Syntax
Abs	Returns the absolute value of a number	Abs(<number>)
Ceil	Returns the lowest integer greater than or equal to a number	Ceil(<number>)
Floor	Returns the greatest integer lower than or equal to a number	Floor(<number>)
Greatest	Returns the greater of two numbers	Greatest(<number 1>, <number 2>)
Least	Returns the smaller of two numbers	Least(<number 1>, <number 2>)
Mod	Returns the remainder of the integer division from n1 by n2	Mod(<number 1>, <number 2>)
Percent	Returns the ratio of two numbers expressed as a percentage	Percent(<number 1>, <number 2>)
Random	Returns the random value	Random()
Round	Rounds off a number to n decimals	Round(<number>, <number of decimals>)
Sign	Returns the sign of the number	Sign(<number>)
ToDouble	Converts an integer to a float	ToDouble(<number>)
ToInt64	Converts a float to a 64 bit integer	ToInt64(<number>)
ToInteger	Converts a float to an integer	ToInteger(<number>)
Trunc	Truncates n1 to n2 decimals	Trunc(<n1>, <n2>)

Others

This table contains the remaining functions available.

Name

Description

Syntax

Case

Returns value 1 if the condition is verified. Otherwise, returns value 2

Case(When(<condition>, <value 1>), Else(<value 2>))

ClearBit

Deletes the Flag in the value

ClearBit(<identifier>, <flag>)

Coalesce

Returns value 2 if value 1 is zero or null, otherwise returns value 1

Coalesce(<value 1>, <value 2>)

Decode

Returns value 3 is value 1 = value 2, otherwise returns 4

Decode(<value 1>, <value 2>, <value 3>, <value 4>)

Else

Returns value 1 (may only be used as a parameter of the case function)

Else(<value 1>)

GetEmailDomain

Extracts the domain from an email address

GetEmailDomain(<value>)

GetMirrorURL

Retrieves the URL of the mirror page server

GetMirrorURL(<value>)

Iif

Returns value 1 if the expression is true, otherwise returns value 2

Iif(<condition>, <value 1>, <value 2>)

IsBitSet

Indicates whether the Flag is in the value

IsBitSet(<identifier>, <flag>)

IsEmptyString

Returns value 2 if the string is empty, otherwise returns value 3

IsEmptyString(<string>, <value 2>, <value 3>)

NoNull

Returns the empty string if the argument is NULL

NoNull(<value>)

RowId

Returns the line number

RowId

SetBit

Forces the Flag in the value

SetBit(<identifier>, <flag>)

ToBoolean

Converts a number into a Boolean

ToBoolean(<number>)

When

Returns value 1 if the expression is verified. Otherwise, returns value 2 (may only be used as a parameter of the case function)

When(<condition>, <value 1>)

newUUID

Returns a new UUID.

newUUID

String

The string functions are used to manipulate a set of strings.

Name

Description

Syntax

AllNonNull2

Indicates if all parameters are non-null and not empty

AllNonNull2(<string>, <string>)

AllNonNull3

Indicates if all parameters are non-null and not empty

AllNonNull3(<string>, <string>, <string>)

ASCII

Returns the ASCII value of the first character in the string

Ascii(<string>)

Char

Returns the character corresponding to the 'n' ASCII code

Char(<number>)

Charindex

Returns the position of string 2 in string 1

Charindex(<string>, <string>)

DataLength

Returns the number of characters in a string

DataLength(<String>)

GetLine

Returns the nth (from 1 to n) line of the string

GetLine(<string>)

IfEquals

Returns the third parameter if the first two parameters are equal otherwise returns the last parameter

IfEquals(<string>, <string>, <string>, <string>)

IsMemoNull

Indicates if the memo passed as a parameter is null

IsMemoNull(<Memo>)

JuxtWords

Concatenates the two strings passed as parameters. A space is added between each string in the returned value.

JuxtWords(<string>, <string>)

JuxtWords3

Concatenates the three strings passed as parameters. A space is added between each string in the returned value.

JuxtWords3(<string>, <string>, <string>)

LPad

Returns the completed string on the left

LPad(<string>, <number>, <caractère>)

Left

Returns the first n characters of the string

Left(<string>, <number>)

Length

Returns the string length

Length(<string>)

Lower

Returns the string in lowercase

Lower(<string>)

Ltrim

Removes spaces to the left of the string

Ltrim(<string>)

Md5Digest

Returns an hexadecimal representation of the MD5 key of a string

Md5Digest(<string>)

MemoContains

Specifies whether the memo contains the string passed as a parameter

MemoContains(<memo>, <string>)

RPad

Returns the completed string on the right

RPad(<string>, <number>, <character>)

Replace

Replaces all occurrences of a specified string (2nd parameter) value with another string value (3rd parameter) in a string (1st parameter)

Replace(<String>, <String>, <String>)

Right

Returns the last n characters of the string

Right(<string>)

Rtrim

Removes spaces to the right of the string

Rtrim(<string>)

Sha256Digest

Calculates the standard SHA256 hash for a given UTF8 string

Sha256Digest(<String>)

Sha384Digest

Calculates the standard SHA384 hash for a given UTF8 string

Sha384Digest(<String>)

Sha512Digest

Calculates the standard SHA512 hash for a given UTF8 string

Sha512Digest(<String>)

Smart

Returns the string with the first letter of each word in capitals

Smart(<string>)

Substring

Extracts the sub-string starting at character n1 of the string and with a length of n2

Substring(<string>, <offset>, <length>)

ToIntlString

Converts the number to a string

ToIntlString(<number>)

ToString

Converts the number to a string

ToString(<number>)

Upper

Returns the string in capitals

Upper(<string>)

VirtualLink

Returns the foreign key of a link passed as a parameter if the other two parameters are equal

VirtualLink(<number>, <number>, <number>)

VirtualLinkStr

Returns the foreign (text) key of a link passed as a parameter if the other two parameters are equal

VirtualLinkStr(<string>, <number>, <number>)

encryption_aescbcDecrypt

Decrypts an encrypted value in HEX format with "x" prefix (1st parameter) using a key in HEX format (2nd parameter) and an initialization vector in HEX format (3rd parameter)

encryption_aescbcDecrypt(<String>, <String>, <String>)

encryption_aescbcEncrypt

Encrypts using AES algorithm (CBC block mode) a string of characters (1st parameter) with a key (2nd parameter) and an initialization vector (3rd parameter). The key and the initialization vector must be given in a hexadecimal representation (starting with \x). The result will be in hexadecimal without the \x.
Please note that key size can be 128 bits, 192 bits, 256 bits (16, 24, 32 hexadecimal characters) but we advise you to use 256 bits and a randomized IV of the same length as the key.

encryption_aescbcEncrypt(<String>, <String>, <String>)
For example: encryption_aescbcEncrypt(johndoe@example.com, "\\x0123456789ABCDEF0123456789ABCDEF", "\\x0123456789ABCDEFFEDCBA9876543210")

Aggregates

The aggregation functions are only available when adding additional data from a workflow’s Query activity.

The aggregate functions are used to perform calculations on a set of values.

Name

Description

Syntax

Avg, Average

Returns the average in a numerical column.

Avg(<value>)

Count, Count (except NULL)

Counts the non-null values in a column.

Count(<value>)

CountAll, Count all

Counts all of the values (including null values and duplicates).

CountAll()

Countdistinct, Distinct count

Counts the non-null, distinct values in a column.

Countdistinct(<value>)

Max, Max

Returns the maximum value in a numerical, string, or date column.

Max(<value>)

Min, Min

Returns the minimum value in a numerical, string, or date column.

Min(<value>)

StringAgg, String aggregate

Returns the concatenation of the values of a string type column, separated by the character in the second argument (default separator is comma).

StringAgg(<string values>,<separator>)

Sum, Sum

Returns the sum of the values in a numerical column.

Sum(<value>)

Representation

The representation functions are used to order values.

Name

Description

Syntax

Desc

Applies a descending sort

Desc(<value 1>)

OrderBy

Sorts the result within the partition

OrderBy(<value 1>)

PartitionBy

Partitions the result of a query on a table

PartitionBy(<value 1>)

RowNum

Generates a line number based on the table partition and on a sorting sequence. This function is not supported for MySQL

RowNum(PartitionBy(<value 1>), OrderBy(<value 1>))

recommendation-more-help