Expression functions
The functions the expression editor offers, grouped as the editor groups them. Each example is written the way you would type it.
Logical
CASE WHEN
CASE WHEN cond THEN a [WHEN …] ELSE b END
Pick a result based on conditions checked top to bottom: SQL's IF / THEN / ELSE. The first matching WHEN wins; ELSE is the fallback when none match.
CASE WHEN [score] >= 90 THEN 'A' WHEN [score] >= 80 THEN 'B' ELSE 'C' END
coalesce
coalesce(a, b, …)
Returns the first value that isn't empty (NULL), reading left to right: the standard way to supply a fallback.
coalesce([nickname], [first_name], 'Friend')
nullif
nullif(a, b)
Returns nothing (NULL) when the two values are equal, otherwise the first value. Handy for turning blanks into true empties.
nullif([region], '')
nvl
nvl(a, b)
Returns the second value when the first is empty (NULL), otherwise the first: a simple one-column fallback.
nvl([discount], 0)
greatest
greatest(a, b, …)
Returns the largest of the values you list.
greatest([q1], [q2], [q3])
least
least(a, b, …)
Returns the smallest of the values you list.
least([bid], [ceiling])
IN
expr IN (a, b, …)
True when the value matches any item in the list: a shorter way to write several OR checks.
[country] IN ('US', 'CA', 'MX')BETWEEN
expr BETWEEN low AND high
True when the value falls within a range, including both end points.
[age] BETWEEN 18 AND 65
IS NULL
expr IS NULL / IS NOT NULL
Checks whether a value is empty (IS NULL) or present (IS NOT NULL).
[email] IS NOT NULL
Text
upper
upper(s)
Converts text to UPPERCASE.
upper([code])
lower
lower(s)
Converts text to lowercase.
lower([email])
initcap
initcap(s)
Capitalizes the first letter of each word (title case).
initcap([full_name])
remove_accents
remove_accents(s)
Takes the accents off Latin, Greek and Cyrillic letters, so José becomes Jose and Łódź becomes Lodz. A letter that has no accent, like the German sharp s, is left as it is, and so are other scripts. Useful for matching names typed with and without accents.
remove_accents([customer_name])
length
length(s)
Counts the number of characters in the text.
length([description])
trim
trim(s)
Removes spaces from both ends of the text (use ltrim or rtrim to trim only one side).
trim([raw])
substr
substr(s, start [, count])
Pulls out part of the text, starting at a position (the first character is 1) for an optional number of characters.
substr([sku], 1, 3)
left
left(s, n)
Takes the first n characters from the start of the text.
left([zip], 5)
right
right(s, n)
Takes the last n characters from the end of the text.
right([phone], 4)
concat
concat(a, b, …)
Joins several values together into one piece of text.
concat([first_name], ' ', [last_name])
concat_ws
concat_ws(sep, a, b, …)
Joins values into text with a separator placed between each one.
concat_ws('-', [year], [month], [day])replace
replace(s, from, to)
Replaces every occurrence of one piece of text with another.
replace([phone], '-', '')
split_part
split_part(s, delim, n)
Splits text on a delimiter and returns the n-th piece, counting from 1.
split_part([email], '@', 2)
starts_with
starts_with(s, prefix)
True when the text begins with the given prefix.
starts_with([path], '/api')
ends_with
ends_with(s, suffix)
True when the text ends with the given suffix.
ends_with([file], '.csv')
contains
contains(s, sub)
True when the text contains the given snippet anywhere inside it.
contains([notes], 'urgent')
strpos
strpos(s, sub)
Finds where a snippet first appears in the text, counting from 1; returns 0 when it isn't found.
strpos([url], '?')
lpad
lpad(s, len [, fill])
Pads the text on the left up to a set length, for example to add leading zeros.
lpad([id], 6, '0')
rpad
rpad(s, len [, fill])
Pads the text on the right up to a set length.
rpad([code], 8)
reverse
reverse(s)
Reverses the order of the characters.
reverse([code])
regexp_match
regexp_match(s, pattern)
Returns the parts of the text captured by a regular-expression pattern, for advanced pattern matching.
regexp_match([sku], '^([A-Z]+)')
regexp_replace
regexp_replace(s, pattern, replacement [, flags])
Replaces text matching a regular-expression pattern; add the 'g' flag to replace every match, not just the first.
regexp_replace([phone], '[^0-9]', '', 'g')
regexp_like
regexp_like(s, pattern)
True when the text matches a regular-expression pattern.
regexp_like([email], '^[^@]+@[^@]+$')
translate
translate(s, from, to)
Swaps characters one for one: each character in from becomes the character at the same position in to. A character with no partner in to is removed: the quick way to strip several characters at once.
translate([phone], '()- ', '')
levenshtein
levenshtein(a, b)
Counts how many single-character edits turn one text into the other; 0 means identical. Small numbers catch typos and near-duplicate spellings.
levenshtein(lower([vendor]), lower([supplier])) <= 2
md5
md5(s)
Returns a fixed 32-character fingerprint of the text. The same input always gives the same output, so a hashed column still joins and groups, but the original value is not shown.
md5(lower([email]))
sha256
sha256(s)
A stronger fingerprint than md5, returned as raw bytes; wrap it in encode(…, 'hex') to get readable text. Use it to mask personal data while keeping it joinable.
encode(sha256(lower([email])), 'hex')
Numbers
abs
abs(x)
Returns the number without its sign (always zero or positive).
abs([balance])
round
round(x [, decimals])
Rounds a number to a given number of decimal places (0 by default).
round([price] * 1.08, 2)
ceil
ceil(x)
Rounds a number up to the next whole number.
ceil([weight])
floor
floor(x)
Rounds a number down to the previous whole number.
floor([weight])
trunc
trunc(x)
Cuts off the decimals without rounding (2.9 becomes 2).
trunc([amount])
power
power(base, exp)
Raises a number to a power: the base raised to the exponent.
power([side], 2)
sqrt
sqrt(x)
Returns the square root of a number.
sqrt([area])
ln
ln(x)
Returns the natural logarithm (base e) of a number.
ln([revenue])
log10
log10(x)
Returns the base-10 logarithm of a number.
log10([count])
exp
exp(x)
Returns e raised to the given power, the inverse of ln.
exp([rate])
signum
signum(x)
Returns -1 for negative values, 0 for zero, and 1 for positive values: a quick sign check.
signum([delta])
%
a % b
The remainder left after dividing (modulo). Everyday math uses + - * / % as normal.
[row_num] % 2 = 0
Date & time
now
now()
The current date and time, in the instance timezone (the one set in Settings, the same clock that fires schedules). Subtract an INTERVAL to move it: now() - INTERVAL '62 days'.
to_char(now() - INTERVAL '62 days', '%Y%m%d')
current_date
current_date
Today's date, in the instance timezone.
current_date
date_part
date_part(field, ts)
Pulls one part out of a date or timestamp: the year, month, day, hour, minute or weekday.
date_part('year', [created_at])extract
extract(field FROM ts)
Another way to pull one part out of a date or timestamp (the SQL EXTRACT form of date_part).
extract(month FROM [created_at])
date_trunc
date_trunc(unit, ts)
Rounds a timestamp down to the start of a unit: the first of the month, the start of the day, and so on.
date_trunc('month', [created_at])date_bin
date_bin(interval, ts, origin)
Groups timestamps into fixed-size time buckets (for example every 15 minutes), useful for time-based rollups.
date_bin(INTERVAL '15 minutes', [event_at], TIMESTAMP '1970-01-01')
to_date
to_date(s [, format])
Turns text into a date, with an optional format that describes how the text is laid out.
to_date([ymd], '%Y-%m-%d')
make_date
make_date(year, month, day)
Builds a date from three numbers, handy when the year, month and day arrive in separate columns.
make_date([fiscal_year], [period], 1)
to_timestamp
to_timestamp(s)
Turns text into a date-and-time value.
to_timestamp([iso_time])
to_char
to_char(ts, format)
Formats a date or timestamp as text using a layout you choose.
to_char([created_at], '%Y-%m')
AT TIME ZONE
ts AT TIME ZONE 'zone'
Reads a timestamp in another zone (an IANA name such as 'America/New_York'): the same instant, shown on that zone's clock. Combine with to_local_time to drop the zone.
to_local_time([event_at] AT TIME ZONE 'America/New_York')
to_local_time
to_local_time(ts)
Strips the zone from a zoned timestamp, keeping the wall-clock time, which is what you want before comparing with a plain date-time column.
to_local_time(now())
to_unixtime
to_unixtime(ts)
Seconds since 1970-01-01 as a number, handy for arithmetic on a timestamp.
to_unixtime([reading_timestamp]) - 900
from_unixtime
from_unixtime(seconds)
Turns seconds since 1970-01-01 back into a timestamp.
from_unixtime(to_unixtime([reading_timestamp]) - 900)
Convert types
cast
cast(x AS type)
Convert a value to another type. Types: INT / BIGINT (whole numbers), DOUBLE (decimal numbers), DECIMAL(p,s) (fixed-precision, e.g. money), VARCHAR (text), BOOLEAN (true/false), DATE (calendar date), TIMESTAMP (date + time).
cast([amount] AS DOUBLE)