Expression Functions Reference

Complete reference for all expression functions available in the projection expression editor, organised by category: text, numeric, date and time, conditional, type conversion, hash, and aggregate.

April 1, 2026

The projection expression editor provides a curated set of approximately 35 functions across 7 categories. Expressions are written as plain strings using SQL-style syntax. Field references use curly braces: {fieldName}. Functions can be nested and combined with arithmetic and logical operators.

Expression Syntax

Expressions support the following constructs.

Construct	Syntax	Example
Field reference	{fieldName}	{customer_id}
String literal	'value'	'active'
Numeric literal	42, 3.14	42
Boolean literal	TRUE, FALSE	TRUE
Null literal	NULL	NULL
Function call	FUNCTION(args)	UPPER({lastName})
Arithmetic	+, -, *, /, %	{price} * {quantity}
Comparison	=, !=, <, >, <=, >=	{status} = 'active'
Logical	AND, OR, NOT	{active} = TRUE AND {age} > 18
String concatenation	\|\|	{firstName} \|\| ' ' \|\| {lastName}
Null checks	IS NULL, IS NOT NULL	{email} IS NOT NULL
Set membership	IN	{status} IN ('active', 'pending')
Pattern matching	LIKE	{email} LIKE '%@example.com'
Type cast	CAST(expr AS type)	CAST({age} AS string)
Conditional	CASE WHEN ... THEN ... ELSE ... END	CASE WHEN {status} = 'active' THEN 1 ELSE 0 END
Grouping	( )	({price} + {tax}) * {quantity}

Text Functions

String manipulation and formatting functions. All text functions accept string or text input types unless otherwise noted.

Function	Syntax	Returns	Description
UPPER	UPPER(value)	string	Convert text to upper case.
LOWER	LOWER(value)	string	Convert text to lower case.
TRIM	TRIM(value)	string	Remove leading and trailing whitespace.
LEFT	LEFT(value, length)	string	Extract the specified number of characters from the start.
RIGHT	RIGHT(value, length)	string	Extract the specified number of characters from the end.
SUBSTRING	SUBSTRING(value, start [, length])	string	Extract a portion of text. Start position is 1-based. Length is optional.
REPLACE	REPLACE(value, search, replacement)	string	Replace all occurrences of the search string with the replacement.
CONCAT	CONCAT(value1, value2, ...)	string	Join two or more values together. Accepts any input type. Variadic (minimum 2 arguments).
LENGTH	LENGTH(value)	integer	Count the number of characters in the text.
SPLIT_PART	SPLIT_PART(value, delimiter, part)	string	Split text by a delimiter and return the specified part (1-based index).

Text Function Examples

UPPER({lastName})
-- Result: 'SMITH'

CONCAT({firstName}, ' ', {lastName})
-- Result: 'John Smith'

SUBSTRING({postCode}, 1, 4)
-- Result: 'SW1A'

REPLACE({phone}, '-', '')
-- Result: '02071234567'

SPLIT_PART({fullName}, ' ', 2)
-- Result: 'Smith' (from 'John Smith')

Numeric Functions

Arithmetic and rounding functions. All accept float, decimal, or integer input types.

Function	Syntax	Returns	Description
ROUND	ROUND(value [, decimals])	decimal	Round to the specified number of decimal places. Defaults to 0 if decimals is omitted.
FLOOR	FLOOR(value)	integer	Round down to the nearest integer.
CEILING	CEILING(value)	integer	Round up to the nearest integer.
ABS	ABS(value)	numeric	Return the absolute (positive) value.
MOD	MOD(value, divisor)	integer	Return the remainder after integer division.

Numeric Function Examples

ROUND({totalAmount}, 2)
-- Result: 149.99

FLOOR({rating})
-- Result: 4 (from 4.7)

CEILING({estimatedHours})
-- Result: 8 (from 7.2)

ABS({balanceChange})
-- Result: 250 (from -250)

MOD({rowNumber}, 2)
-- Result: 0 (even rows) or 1 (odd rows)

Date and Time Functions

Date extraction, arithmetic, and formatting functions. Accept date or datetime input types.

Function	Syntax	Returns	Description
YEAR	YEAR(date)	integer	Extract the year component from a date.
MONTH	MONTH(date)	integer	Extract the month component (1-12) from a date.
DAY	DAY(date)	integer	Extract the day of the month from a date.
DATE_DIFF	DATE_DIFF(unit, start_date, end_date)	integer	Calculate the difference between two dates in the specified unit. Units: day, month, year, hour, minute.
DATE_ADD	DATE_ADD(date, amount, unit)	datetime	Add an interval to a date. Units: day, month, year, hour, minute.
FORMAT_DATE	FORMAT_DATE(date, format)	string	Format a date as text using a pattern string (e.g. YYYY-MM-DD, DD/MM/YYYY).
CURRENT_TIMESTAMP	CURRENT_TIMESTAMP	datetime	Return the current date and time. Takes no arguments.

Date Function Examples

YEAR({createdDate})
-- Result: 2026

DATE_DIFF('day', {startDate}, {endDate})
-- Result: 30

DATE_ADD({orderDate}, 14, 'day')
-- Result: 2026-04-15 (if orderDate is 2026-04-01)

FORMAT_DATE({publishedDate}, 'DD MMM YYYY')
-- Result: '01 Apr 2026'

CURRENT_TIMESTAMP
-- Result: 2026-04-01T14:30:00Z

Conditional Functions

Null handling and branching functions. Accept any input type.

Function	Syntax	Returns	Description
COALESCE	COALESCE(value1, value2, ...)	any	Return the first non-null value from the argument list. Variadic (minimum 2 arguments).
NULLIF	NULLIF(value, compare)	any	Return null if the two values are equal; otherwise return the first value.
IFNULL	IFNULL(value, default)	any	Return the default value if the input is null; otherwise return the input value.

Conditional Function Examples

COALESCE({preferredName}, {firstName}, 'Unknown')
-- Returns preferredName if not null, else firstName, else 'Unknown'

NULLIF({status}, 'N/A')
-- Returns null if status is 'N/A', else returns the status value

IFNULL({middleName}, '')
-- Returns '' if middleName is null

Type Conversion Functions

Functions for explicit type conversion between abstract types.

Function	Syntax	Returns	Input Types	Description
CAST	CAST(value AS target_type)	any	any	Convert a value to the specified abstract type. Target type must be a valid abstract type name.
TO_STRING	TO_STRING(value)	string	any	Convert any value to its text representation.
TO_INTEGER	TO_INTEGER(value)	integer	string, float, decimal	Convert a value to an integer. Strings are parsed; floats and decimals are truncated.
TO_DECIMAL	TO_DECIMAL(value)	decimal	string, integer, float	Convert a value to a decimal number.
TO_DATE	TO_DATE(value [, format])	date	string, datetime	Convert a text value to a date. Optional format pattern specifies the source format.

Type Conversion Examples

CAST({age} AS string)
-- Result: '25'

TO_INTEGER({quantity_text})
-- Result: 42

TO_DATE({dateString}, 'DD/MM/YYYY')
-- Result: 2026-04-01

Hash and Encoding Functions

Hashing and encoding functions for data masking, key generation, and encoding.

Function	Syntax	Returns	Input Types	Description
SHA256	SHA256(value)	string	any	Generate a SHA-256 hash of the input value. Returns a 64-character hexadecimal string.
MD5	MD5(value)	string	any	Generate an MD5 hash of the input value. Returns a 32-character hexadecimal string.
BASE64_ENCODE	BASE64_ENCODE(value)	string	string	Encode a text value as Base64.
BASE64_DECODE	BASE64_DECODE(value)	string	string	Decode a Base64 encoded value back to text.

Hash Function Examples

SHA256(CONCAT({customerId}, {orderDate}))
-- Result: 'a3f2b8c1...' (64-char hex string)

MD5({email})
-- Result: 'd41d8cd9...' (32-char hex string)

BASE64_ENCODE({apiKey})
-- Result: 'YXBpLWtleS0xMjM0NQ=='

Aggregate Functions

Grouping and summarisation functions. These are available both as expression functions and as the aggregateFunction field on aggregate transformation types.

Function	Syntax	Returns	Input Types	Description
COUNT	COUNT(value)	integer	any	Count the number of values in the group.
SUM	SUM(value)	numeric	integer, float, decimal	Sum all numeric values in the group.
AVG	AVG(value)	decimal	integer, float, decimal	Calculate the arithmetic mean of all values.
MIN	MIN(value)	any	any	Return the smallest value in the group.
MAX	MAX(value)	any	any	Return the largest value in the group.
COUNT_DISTINCT	COUNT_DISTINCT(value)	integer	any	Count the number of unique values in the group.
STRING_AGG	STRING_AGG(value, separator)	string	string	Concatenate all string values in the group with the specified separator character.

Aggregate Function Examples

COUNT({orderId})
-- Result: 150

SUM({lineTotal})
-- Result: 24750.00

AVG({rating})
-- Result: 4.3

STRING_AGG({tagName}, ', ')
-- Result: 'urgent, high-priority, customer-facing'

Function Categories

The expression editor groups functions into 7 categories for browsing in the function picker.

Category	Icon	Functions
Text	Type icon	UPPER, LOWER, TRIM, LEFT, RIGHT, SUBSTRING, REPLACE, CONCAT, LENGTH, SPLIT_PART
Numeric	Hash icon	ROUND, FLOOR, CEILING, ABS, MOD
Date and Time	Calendar icon	YEAR, MONTH, DAY, DATE_DIFF, DATE_ADD, FORMAT_DATE, CURRENT_TIMESTAMP
Conditional	Branch icon	COALESCE, NULLIF, IFNULL
Type	Arrows icon	CAST, TO_STRING, TO_INTEGER, TO_DECIMAL, TO_DATE
Hash	Fingerprint icon	SHA256, MD5, BASE64_ENCODE, BASE64_DECODE
Aggregate	Bar chart icon	COUNT, SUM, AVG, MIN, MAX, COUNT_DISTINCT, STRING_AGG