DocumentationCampaignCampaign Standard Documentation

List of functions list-of-functions

Last update: Tue Sep 21 2021 00:00:00 GMT+0000 (Coordinated Universal Time)

CREATED FOR:

  • Experienced
  • Developer

About functions 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 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 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 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 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 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 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 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
3ef63344-7f3d-48f9-85ed-02bf569c4fff