Savant

List of operators for use in Edit and Filter expressions.

Operators

Functions Guide

Learn where and how to use functions and expressions in Savant

How to - Functions and Expressions

Transform Tool Multi-Row Functions

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 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

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

COALESCE

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

FIRST_NOT_EMPTY

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

Returns a value corresponding to the first true condition

IS_EMPTY

JSON_FIELD

Sets a value for a specific field in JSON data

JSON_SET

Other

Concatenates multiple text strings into a single string

CONCAT

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

Retrieves the leftmost characters from a text string

LEFT_SUBSTR

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

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

Extracts the rightmost characters of a text string up to a specified length

RIGHT_SUBSTR

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

SEARCH

Converts text to a SOUNDEX code for phonetic matching

SOUNDEX

Extracts a specific part of a text string using a delimiter

SPLIT_PART

Extracts a portion of a text string starting from a specified position with a given length