List of functions

Last update: 2021-09-21
  • 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>))
Previous page
Next page

On this page