Savant

List of operators for use in Edit and Filter expressions.

Operators

Functions Guide

Functions, Operators & Expressions

Creates a date based on the provided year, month, and day values

DATE

Adds a specified offset of time (e.g., seconds, minutes, hours) to a given date or datetime

DATE_ADD

Calculates the difference in days, weeks, months, quarters, or years between two dates

DATE_DIFF

Creates a datetime based on the provided year, month, day, hour, minute, and second values

DATETIME

Retrieves the day of the month from a given date or datetime

Determines the day of the week for a given date or datetime, where Monday is 1, Tuesday is 2, and so on

DAY_OF_WEEK

Calculates the day of the year for a given date or datetime

DAY_OF_YEAR

Calculates the number of days in the month for a given date or datetime

DAYS_IN_MONTH

Calculates the number of days in a year for a given date or datetime, considering leap years

DAYS_IN_YEAR

Extracts the hour component from a given datetime

HOUR

Adds a specified number of days, weeks, months, quarters, or years to a date

INTERVAL (Date)

Adds a specified number of seconds, minutes, or hours to a datetime

INTERVAL (DateTime)

Extracts the minute component from a given datetime

MINUTE

Retrieves the month component from a given date

MONTH

Retrieves the quarter of the year from a given date or datetime

QUARTER

Extracts the second component from a given datetime

SECOND

Returns a standard time period (e.g., day, week, month, quarter, year) for a given date or datetime field

TIME_PERIOD

Calculates the number of time periods between a given date and the current period

TIME_PERIOD_OFFSET

TODAY

Determines the week number of the year for a given date or datetime

WEEK/WEEK_OF_YEAR

Retrieves the year component from a given date or datetime

YEAR

Date and DateTime

Converts a value to a boolean (true/false) format

TO_BOOLEAN

TO_DATE

TO_DATETIME

TO_INTEGER

TO_NUMBER

Converts a value, whether it's a datetime or any other data type, to a text format

TO_TEXT

Formatting

Evaluates conditions and returns a value based on the first true condition

CASE ... WHEN ...

Checks if a value exists within a specified set of values

Keywords

Returns one value if a condition is true and another if false.

Returns a value corresponding to the first true condition

Logical

Returns the absolute value of a number, ignoring its sign (+/-)

Rounds a number up to the nearest integer

CEILING

Rounds a number down to the nearest integer

FLOOR

Returns the greatest value among the given columns (i.e., arguments)

GREATEST

Returns the smallest value among the given columns (i.e., arguments)

LEAST

Calculates the logarithm of a number to a specified base

Returns the remainder of a division operation

Raises a number to the power of another number

POWER

Generates a random decimal value between 0 and 1

RANDOM

Rounds a number to a specified number of decimal places

ROUND

ROUNDDOWN

ROUNDUP

Math

Calculates the average value of a specified numeric field across a range or partition

Combines unique text values from multiple rows in a specified field into a single text value

CONCAT

CONCAT DISTINCT

Counts the number of rows within a specified range or partition

COUNT

Counts the number of unique values in a specified field within a range or partition

COUNT DISTINCT

Calculates the cumulative sum of a specified numeric field across a range or partition

CUMULATIVE SUM

Populates missing values in a specified field using a defined strategy, such as forward fill or backward fill

FILL

Get the first value of a specified field within a range or partition

FIRST

Get the value of a specified field from a previous row in the dataset

Get the last value of a specified field within a range or partition

LAST

Get the value of a specified field from a subsequent row in the dataset.

LEAD

Get the maximum value of a specified field within a range or partition

Get the median value of a specified numeric field within a range or partition

MEDIAN

Get the minimum value of a specified field within a range or partition

Assigns a bucket number to each row in a partition

NTILE

Assigns a rank to each row within a partition of the dataset, with ties receiving the same rank

RANK

ROW NUMBER

Calculates the standard deviation of a specified numeric field within a range or partition

STDDEV

Calculates the total sum of a specified numeric field within a range or partition

Calculates the variance of a specified numeric field within a range or partition.

Multi-Row Aggregation & Windowing

Returns the first non-null value from a list of options.

COALESCE

Concatenates multiple text strings into a single string

Checks if text contains specified subtext.

CONTAINS

Returns the first non-empty value from a list of options.

FIRST_NOT_EMPTY

Computes the hash value of a text using a specified algorithm (MD5 or SHA-1)

HASH

Escapes HTML special characters in a text string

HTML_ESCAPE

IS_EMPTY

JSON_FIELD

Sets a value for a specific field in JSON data

JSON_SET

Retrieves the leftmost characters from a text string.

LEFT_PART

Computes the length (number of characters) of a text string

LENGTH

Measures the edit distance (Levenshtein distance) between two text strings

LEVENSHTEIN

Converts text to all lowercase characters

LOWER

Pads the left-side of text with a specified character to ensure a minimum text length

PAD_LEFT

Pads the right-side of text with a specified character to ensure a minimum text length

PAD_RIGHT

Capitalizes the first letter of each word in a text string

PROPER

Extracts text that matches a regular expression pattern

REGEX_EXTRACT

Checks if a text string matches a specified regular expression pattern

REGEX_MATCH

Replaces text that matches a regular expression pattern with a specified replacement

REGEX_REPLACE

Returns the index of the first character of the first matching substring based on a regular expression pattern

REGEX_SEARCH

Repeats a specified string a given number of times.

REPEAT()

Replaces all occurrences of a specified search string with a replacement string

REPLACE

Reverses the order of characters in a text string

REVERSE

Retrieves the rightmost characters from a text string.

RIGHT_PART

Searches for a specific search string within a text and returns the index of the first occurrence