Functions glossary
All Firebolt functions in alphabetical order.
| Name | Function | Type |
|---|---|---|
| ABS | Calculates the absolute value of an input value. | Numeric |
| ANY_MATCH | Returns 1 if at least one of the elements of an array matches the results of the function provided. Otherwise returns 0. | Lambda |
| ARRAY_CONCAT | Combines one or more arrays that are passed as arguments. | 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_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_JOIN (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_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. If the argument function is provided, the values of the array elements are converted by this function before summing. | Lambda |
| AVG | Calculates the average of an expression. | Aggregation |
| AVG OVER | Returns the average value within the requested window. | Window |
| 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 |
| CONTAINS | Returns 1 if a specified argument is present in the array, or 0 otherwise. | Array |
| 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 |
| 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 |
| 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 |
| 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 |
| MATCH | Checks whether the input expression matches the specified regular expression pattern, which is a RE2 regular expression. Returns 0 if it doesn’t match, or 1 if it matches. | String |
| MAX | Calculates the maximum value of an expression across all input values. | 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 OVER | Returns the maximum value within the requested window. | Window |
| MOD | Calculates the remainder after dividing two values. | Numeric |
| 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 |
| RANDOM | Returns a pseudo-random unsigned value greater than 0 and less than 1 of type DOUBLE PRECISION. | Numeric |
| 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_MATCHES | Returns an array that contains either the match or all defined subgroups of the first match of the regular expression pattern. Returns an empty array if the pattern does not match. | 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 |
| 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 string is divided into an array of single characters. | 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 |