Link Search Menu Expand Document

Functions glossary

All Firebolt functions in alphabetical order.

Name Function Type
ABS Calculates the absolute value of an input value. Numeric
ARRAY_ANY_MATCH Returns true if the function provided returns true for at least one of the elements of an array. Otherwise returns false. Lambda
ARRAY_CONCAT Combines one or more arrays that are passed as arguments. Array
ARRAY_CONTAINS Returns 1 if a specified argument is present in the array, or 0 otherwise. Array
ARRAY_COUNT Counts the number of elements in a boolean array for which function(array[i]) evaluates to true, if a function is provided. If not provided, counts the number of elements in the array that evaluate to true. Lambda
ARRAY_DISTINCT Returns an array containing only the unique elements of the given array. Array
ARRAY_FIRST Returns the first element in the given array for which the given function returns true. Lambda
ARRAY_FLATTEN Converts an array of arrays into a flat array. For every element that is an array, this function extracts its elements into the new array. Array
ARRAY_TO_STRING Concatenates an array of string elements using an optional delimiter. Array
ARRAY_LENGTH Returns the length of (number of elements in) the given array. Array
ARRAY_MAX Returns the maximum element in an array. Array
ARRAY_MIN Returns the minimum element in an array. Array
ARRAY_REVERSE Returns an array of the same size and type as the original array, with the elements in reverse order. Array
ARRAY_REVERSE_SORT Returns the elements of the input array in descending order. If the argument function is provided, the sorting order is determined by the result of applying the function on each element of the array. Lambda
ARRAY_SORT Returns the elements of the input array in ascending order. If the argument function is provided, the sorting order is determined by the result of applying the function on each element of the array. Lambda
ARRAY_SUM Returns the sum of elements of the input array. Lambda
AVG Calculates the average of an expression. Aggregation
AVG OVER Returns the average value within the requested window. Window
BOOL_AND Returns true if all non NULL input value are true. Aggregation
BOOL_OR Returns true if any non NULL input value is true. Aggregation
BTRIM Removes all occurrences of optionally specified characters from both sides of a source string. If no trim parameter is specified, all occurrences of common whitespace (ASCII Decimal 32) characters from both sides of the specified source string are removed. String
CAST Converts data types into other data types based on specified parameters. Conditional & miscellaneous
CHECKSUM Calculates a hash value known as a checksum operation on a list of arguments. Aggregation
COALESCE Checks from left to right for the first non-NULL argument found for each entry parameter pair. Conditional & miscellaneous
CONCAT or || Concatenates the strings listed in the input without a separator. String
COUNT Counts the number of rows or not NULL values. Aggregation
COUNT OVER Count the number of values within the requested window. Window
CURRENT_DATE Returns the current (local) date in the time zone specified in the session’s time_zone setting. Date & time
CURRENT_TIMESTAMP Returns the current (local) timestamp in the time zone specified in the session’s time_zone setting. Date & time
DATE_ADD Calculates a new date or timestamp by adding or subtracting a specified number of time units from an indicated expression. Date & time
DATE_DIFF Calculates the difference between the start and end date by the indicated unit. Date & time
DATE_TRUNC Truncates a date or timestamp value to the selected precision. Date & time
DENSE_RANK Rank the current row within the requested window without gaps. Window
EXTRACT Retrieves the time unit from a date or timestamp value. Date & time
GENERATE_SERIES Generates a single rowset of values from start to stop, with a step size of step - a table-valued function. Conditional & miscellaneous
GREATEST Selects the largest value from a list of any number of expressions. Conditional & miscellaneous
IFNULL Compares two expressions. Returns the first input expression if it’s non-NULL, otherwise returns the second. Conditional & miscellaneous
ILIKE Allows matching of strings based on comparison to a pattern, case-insensitively. String
INDEX_OF Returns the index position of the first occurrence of the element in the array (or 0 if not found). Array
JSON_EXTRACT Takes an expression containing a JSON document, a JSON pointer expression, and an expected data type parameter. If the key specified using the JSON pointer expression exists, and its type conforms with the expected data type parameter, returns the value of the data type specified. Otherwise, returns NULL. Semi-structured data
JSON_EXTRACT_ARRAY_RAW Returns a string representation of a JSON array pointed by the supplied JSON pointer. Semi-structured data
JSON_EXTRACT_KEYS Returns an array of strings containing the keys at the nesting level indicated by the specified JSON pointer. Semi-structured data
JSON_EXTRACT_RAW Returns a string representation of the scalar or sub-object under the key indicated by the specified JSON pointer if the key exists. Semi-structured data
JSON_EXTRACT_VALUES Returns an array of string values from a JSON document using the key location specified by the specifed JSON pointer. Semi-structured data
LEAST Selects the smallest value from a list of any number of expressions. Conditional & miscellaneous
LENGTH Calculates the length of the input string. String
LIKE Allows matching of strings based on comparison to a pattern, case-sensitively. String
LOCALTIMESTAMP Returns the current local timestamp in the time zone specified in the session’s time_zone setting. Date & time
LOWER Converts the input string to lowercase characters. String
LPAD Adds a specified pad string to the start of the string repetitively up until the length of the resulting string is equivalent to an indicated length. String
LTRIM Removes all occurrences of optionally specified characters from the left side of a source string. If no trim parameter is specified, all occurrences of common whitespace (ASCII Decimal 32) characters from the left side of the specified source string are removed. String
MAX Calculates the maximum value of an expression across all input values. Aggregation
MAX_BY Returns the value of its first argument for the row that contains the maximum value of the second argument Aggregation
MAX OVER Returns the maximum value within the requested window. Window
MAX_BY Returns the value of the specified input column at the row with the maximum value in the specified value column. Aggregation
MIN Calculates the minimum value of an expression across all input values. Aggregation
MIN_BY Returns the value of its first argument for the row that contains the minimum value of the second argument Aggregation
MIN OVER Returns the maximum value within the requested window. Window
MOD Calculates the remainder after dividing two values. Numeric
NTILE Divides an ordered set into a number of buckets. Window
NULLIF Compares two expressions. Returns NULL if the expressions are equal. Returns the result of the first if they are not equal. Conditional & miscellaneous
OCTET_LENGTH Calculates the length of the input string in bytes. String
PERCENT_RANK Calculates the relative rank of the current row within the requested window. Window
POSITION Returns the position of the substring found in the string, starting from 1. The returned value is for the first matching value, and not for any subsequent valid matches. In case the substring does not exist, position will return 0. String
RANDOM Returns a pseudo-random unsigned value greater than 0 and less than 1 of type DOUBLE PRECISION. Numeric
RANK Rank the current row within the requested window with gaps. Window
REGEXP_LIKE Checks whether a text pattern matches a regular expression string. Returns 0 if it doesn’t match, or 1 if it matches. String
REGEXP_REPLACE Matches a pattern in the input string and replaces the first matched portion (from the left) with the specified replacement. String
REPLACE Replaces all occurrences of the given pattern substring within the input expression with a replacement substring. String
ROUND Rounds a value to a specified number of decimal places. Numeric
ROW_NUMBER Calculate a unique, increasing row number for each row within the window. Window
RPAD Adds a specified pad string to the end of the string repetitively up until the length of the resulting string is equivalent to an indicated length. String
RTRIM Removes all occurrences of optionally specified characters from the right side of a source string. If no trim parameter is specified, all occurrences of common whitespace (ASCII Decimal 32) characters from the right side of the specified source string are removed. String
SPLIT_PART Divides a string based on a specified delimiter into an array of substrings. The string in the specified index is returned, with 1 being the first index. If the string separator is empty, the input string is returned at index 1. String
STRING_TO_ARRAY Splits a given string by a given separator and returns the result in an array of strings. String
STRPOS Returns the position of the substring found in the string, starting from 1. The returned value is for the first matching value, and not for any subsequent valid matches. In case the substring does not exist, strpos will return 0. String
SUBSTRING, SUBSTR Returns a substring starting at the character indicated by the start index and including the number of characters defined. Character indexing starts at index 1. String
SUM Calculates the sum of an expression. Aggregation
SUM OVER Calculate the sum of the values within the requested window. Window
TO_TIMESTAMP Converts a string to timestamp with time zone. Date & time
TRIM Removes all specified characters from the start, end, or both sides of a string. By default removes all consecutive occurrences of common whitespace (ASCII character 32) from both ends of a string. String
TRY_CAST Converts data types into other data types based on the specified parameters. If the conversion cannot be performed, returns a NULL. Conditional & miscellaneous
UPPER Converts the input string to uppercase characters. String
VERSION Returns the version number information for the Firebolt engine. Conditional & miscellaneous