> ## Documentation Index > Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt > Use this file to discover all available pages before exploring further. > Reference for SQL functions available in Firebolt. # Functions glossary The following is a complete alphabetical list of all Firebolt functions: | Name | Function | Type | | :---------------------------------------------------------------------------------------------------------------------- | :-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :-------------------------- | | [ABS](/reference-sql/functions-reference/numeric/abs) | Calculates the absolute value of an input value. | Numeric | | [ACOS](/reference-sql/functions-reference/numeric/acos) | Calculates the arccosine of a value in radians. | Numeric | | [AGO](/reference-sql/functions-reference/date-and-time/ago) | Returns a timestamp that is the specified interval before the current timestamp in the time zone specified in the session's `time_zone` setting. | Date & time | | [APACHE\_DATASKETCHES\_HLL\_BUILD](/reference-sql/functions-reference/datasketches/apache-datasketches-hll-build) | Creates a new Apache DataSketch HyperLogLog sketch. | DataSketches | | [APACHE\_DATASKETCHES\_HLL\_ESTIMATE](/reference-sql/functions-reference/datasketches/apache-datasketches-hll-estimate) | Extracts a cardinality estimate of a single Apache DataSketch HyperLogLog sketch. | DataSketches | | [APACHE\_DATASKETCHES\_HLL\_MERGE](/reference-sql/functions-reference/datasketches/apache-datasketches-hll-merge) | Merges one or more Apache DataSketch HyperLogLog sketches. | DataSketches | | [ARRAYS\_OVERLAP](/reference-sql/functions-reference/array/arrays-overlap) | Returns whether all input arrays have at least one common, non-`NULL` element. | Array | | [ARRAY\_ALL\_MATCH](/reference-sql/functions-reference/lambda/array-all-match) | Returns `TRUE` if the provided function evaluates to `TRUE` for all elements in an array, and `FALSE` otherwise. | Lambda | | [ARRAY\_ANY\_MATCH](/reference-sql/functions-reference/lambda/array-any-match) | Returns `TRUE` if the provided function evaluates to `TRUE` for any element in an array, and `FALSE` otherwise. | Lambda | | [ARRAY\_CONCAT](/reference-sql/functions-reference/array/array-concat) | Combines one or more arrays provided as arguments into a single array. | Array | | [ARRAY\_CONTAINS](/reference-sql/functions-reference/array/array-contains) | Returns `TRUE` if a specified argument is present in the array, and `FALSE` otherwise. | Array | | [ARRAY\_COUNT](/reference-sql/functions-reference/array/array-count) | Counts the number of elements in a boolean array for which the function applied to `(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](/reference-sql/functions-reference/array/array-distinct) | Returns an array containing only the distinct elements of the given array. | Array | | [ARRAY\_FIRST](/reference-sql/functions-reference/lambda/array-first) | Returns the first element in the given array for which the given function returns `TRUE`. | Lambda | | [ARRAY\_FLATTEN](/reference-sql/functions-reference/array/flatten) | Converts a nested array of arrays into a flat array by extracting all the elements from the inner arrays into a single, continuous array. | Array | | [ARRAY\_INTERSECT](/reference-sql/functions-reference/array/array-intersect) | Finds the intersection of the provided argument arrays. | Array | | [ARRAY\_TO\_STRING](/reference-sql/functions-reference/array/array-to-string) | Concatenates the elements of a string array into a single string, using an optional specified delimiter between each element. | Array | | [ARRAY\_LENGTH](/reference-sql/functions-reference/array/array-length) | Returns the length, or number of elements, in the given array. | Array | | [ARRAY\_MAX](/reference-sql/functions-reference/array/array-max) | Returns the maximum element in an array. | Array | | [ARRAY\_MIN](/reference-sql/functions-reference/array/array-min) | Returns the minimum element in an array. | Array | | [ARRAY\_REVERSE](/reference-sql/functions-reference/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](/reference-sql/functions-reference/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](/reference-sql/functions-reference/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](/reference-sql/functions-reference/array/array-sum) | Returns the sum of elements of the input array. | Array | | [ARRAY\_UPPER](/reference-sql/functions-reference/array/array-upper) | Returns the maximum index of an array. | Array | | [ARRAY\_TRANSFORM](/reference-sql/functions-reference/lambda/transform) | Returns an array constructed by applying a lambda function to each element of the input arrays. | Lambda | | [ASIN](/reference-sql/functions-reference/numeric/asin) | Calculates the arcsine of a value in radians. | Numeric | | [ATAN](/reference-sql/functions-reference/numeric/atan) | Calculates the arctangent of a value in radians. | Numeric | | [AVG](/reference-sql/functions-reference/aggregation/avg) | Calculates the average of an expression. | Aggregation | | [AVG OVER](/reference-sql/functions-reference/window/avg-window) | Returns the average value within the requested window. | Window | | [AI\_EMBED\_TEXT](/reference-sql/functions-reference/ai/ai-embed-text) | Generates a vector embedding for input text using an embedding model. | AI | | [AI\_QUERY](/reference-sql/functions-reference/ai/ai-query) | Invokes a large language model (LLM) with a simple prompt. | AI | | [AWS\_BEDROCK\_AI\_QUERY](/reference-sql/functions-reference/ai/aws-bedrock-ai-query) | Invokes an AWS Bedrock model. | AI | | [BIT\_AND](/reference-sql/functions-reference/aggregation/bit_and) | Performs a bitwise `AND` operation on an integer expression. | Aggregation | | [BIT\_OR](/reference-sql/functions-reference/aggregation/bit_or) | Performs a bitwise `OR` operation on an integer expression. | Aggregation | | [BIT\_XOR](/reference-sql/functions-reference/aggregation/bit_xor) | Performs a bitwise `XOR` operation on an integer expression. | Aggregation | | [BOOL\_AND](/reference-sql/functions-reference/aggregation/bool_and) | Returns `TRUE` if all `NOT NULL` values in the input are `TRUE`. | Aggregation | | [BOOL\_OR](/reference-sql/functions-reference/aggregation/bool_or) | Returns `TRUE` if any `NOT NULL` value in the input is `TRUE`. | Aggregation | | [BTRIM](/reference-sql/functions-reference/string/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](/reference-sql/functions-reference/conditional-and-miscellaneous/cast) | Converts data types into other data types based on the specified parameters. | Conditional & miscellaneous | | [CEIL](/reference-sql/functions-reference/numeric/ceil) | Rounds an input `` up to the nearest multiple based on the specified precision or decimal place. | Numeric | | [CHECK\_JSON](/reference-sql/functions-reference/json/check-json) | Returns `NULL` if the input string is a valid JSON document or `NULL`. Otherwise, returns an error message. | Semi-structured data | | [COS](/reference-sql/functions-reference/numeric/cos) | Calculates the cosine of a value in radians. | Numeric | | [COT](/reference-sql/functions-reference/numeric/cot) | Calculates the cotangent of a value in radians. | Numeric | | [HASH\_AGG](/reference-sql/functions-reference/aggregation/hash-agg) | Calculates a hash value across all rows based on a specified list of arguments. | Aggregation | | [CITY\_HASH](/reference-sql/functions-reference/conditional-and-miscellaneous/city-hash) | Takes one or more input parameters of any data type and returns a 64-bit non-cryptographic hash value. Returns `NULL` if any input value is `NULL`. | Conditional & miscellaneous | | [COALESCE](/reference-sql/functions-reference/conditional-and-miscellaneous/coalesce) | Checks from left to right for the first `NOT NULL` argument found for each parameter pair. | Conditional & miscellaneous | | [CONCAT or \|\|](/reference-sql/functions-reference/string/concat) | Concatenates the input strings listed in the input together without adding a separator between them. | String | | [CONVERT\_FROM](/reference-sql/functions-reference/bytea/convert-from) | Converts a binary string encoded in a specified format to sql `TEXT` using the database's default encoding. | Binary | | [CORR](/reference-sql/functions-reference/aggregation/corr) | Returns the correlation between the values of two variables. | Aggregation | | [COUNT](/reference-sql/functions-reference/aggregation/count) | Counts the number of rows or the number of `NOT NULL` values. | Aggregation | | [COUNT OVER](/reference-sql/functions-reference/window/count-window) | Counts the number of values within a specified window. | Window | | [COUNTING\_HLL\_COUNT\_DISTINCT](/reference-sql/functions-reference/aggregation/counting-hll-count-distinct) | Counts the approximate number of unique or not NULL values using the counting HLL sketches. | Aggregation | | [COVAR\_POP](/reference-sql/functions-reference/aggregation/covar-pop) | Returns the population covariance between the values of two variables. | Aggregation | | [COVAR\_SAMP](/reference-sql/functions-reference/aggregation/covar-samp) | Returns the sample covariance between the values of two variables. | Aggregation | | [CURRENT\_ACCOUNT](/reference-sql/functions-reference/session/current-account) | Returns the current account name. | Session | | [CURRENT\_DATABASE](/reference-sql/functions-reference/session/current-database) | Returns the current database name. If no database is set, returns `account_db`. | Session | | [CURRENT\_DATE](/reference-sql/functions-reference/date-and-time/current-date) | Returns the current (local) date in the time zone specified in the session's `time_zone` setting. | Date & time | | [CURRENT\_ENGINE](/reference-sql/functions-reference/session/current-engine) | Returns the current engine name. Returns `system` on the system engine. | Session | | [CURRENT\_TIMESTAMP](/reference-sql/functions-reference/date-and-time/current-timestamptz) | Returns the current (local) timestamp in the time zone specified in the session's `time_zone` setting. | Date & time | | [DATE](/reference-sql/functions-reference/date-and-time/date) | Converts a `TIMESTAMP`, `TIMESTAMPTZ`, `DATE` or `TEXT` value to a `DATE` value. | Date & time | | [DATE\_ADD](/reference-sql/functions-reference/date-and-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](/reference-sql/functions-reference/date-and-time/date-diff) | Calculates the difference between the start and end date based on the specified unit. | Date & time | | [DATE\_TRUNC](/reference-sql/functions-reference/date-and-time/date-trunc) | Truncates a date or timestamp value to the selected precision. | Date & time | | [DECODE](/reference-sql/functions-reference/bytea/decode) | Decodes binary data from a SQL expression of type `TEXT` into its original form. | Binary | | [DEGREES](/reference-sql/functions-reference/numeric/degrees) | Converts a value in radians to degrees. | Numeric | | [DENSE\_RANK](/reference-sql/functions-reference/window/dense-rank) | Ranks the current row within the specified window, ensuring that there are no gaps in the ranking sequence. | Window | | [ENCODE](/reference-sql/functions-reference/bytea/encode) | Encodes binary data from its original form into a SQL expression of type `TEXT`. | Binary | | [EXTRACT](/reference-sql/functions-reference/date-and-time/extract) | Retrieves the time unit from a date or timestamp value. | Date & time | | [FIRST\_VALUE](/reference-sql/functions-reference/window/first-value) | Returns the first value evaluated in the specified window frame. If there are no rows in the window frame, `FIRST_VALUE` returns `NULL`. | Window | | [FLOOR](/reference-sql/functions-reference/numeric/floor) | Rounds an input `` down to the nearest multiple based on the specified precision or decimal place. | Numeric | | [GENERATE\_SERIES](/reference-sql/functions-reference/table-valued/generate-series) | A table-valued function that generates a sequence of numeric values or dates, starting from `start` to `stop`, incremented by a `step` value. | Table-valued | | [GREATEST](/reference-sql/functions-reference/conditional-and-miscellaneous/greatest) | Selects the largest value from a list of expressions. | Conditional & miscellaneous | | [GROUPING](/reference-sql/functions-reference/aggregation/grouping) | Returns a bitwise integer that uniquely identifies whether columns are part of an aggregation or not. | Aggregation | | [HASH](/reference-sql/functions-reference/conditional-and-miscellaneous/hash) | Computes a 64-bit non-cryptographic hash for one or more input values, with `NULL` values always producing the same hash. | Conditional & miscellaneous | | [HLL\_COUNT\_BUILD](/reference-sql/functions-reference/aggregation/hll-count-build) | Counts the approximate number of unique `NOT NULL` values and aggregates them into HLL++ sketches represented as the [BYTEA data type](/reference-sql/data-types/bytea). | Conditional & miscellaneous | | [HLL\_COUNT\_ESTIMATE](/reference-sql/functions-reference/numeric/hll-count-estimate) | Provides a cardinality estimate of a single HLL++ sketch that was previously built using the aggregate function [HLL\_COUNT\_BUILD](/reference-sql/functions-reference/aggregation/hll-count-build). | Conditional & miscellaneous | | [HLL\_COUNT\_MERGE](/reference-sql/functions-reference/aggregation/hll-count-merge) | Merges one or more HLL++ sketches that were previously built using the aggregate function [HLL\_COUNT\_BUILD](/reference-sql/functions-reference/aggregation/hll-count-build) into a new sketch. | Conditional & miscellaneous | | [ICU\_NORMALIZE](/reference-sql/functions-reference/string/icu_normalize) | Transliterate a string given an ICU transliterate id expression. | String | | [IF](/reference-sql/functions-reference/conditional-and-miscellaneous/if) | Emulates IF-THEN-ELSE statement expression. | Conditional & miscellaneous | | [IFNULL](/reference-sql/functions-reference/conditional-and-miscellaneous/ifnull) | Compares two expressions. Returns the first expression if it is non-NULL, and otherwise returns the second expression. | Conditional & miscellaneous | | [ILIKE](/reference-sql/functions-reference/string/ilike) | Case insensitive pattern of strings. | String | | [INDEX\_OF](/reference-sql/functions-reference/array/index-of) | Returns the index position of the first occurrence of the element in an array, or NULL if not found. | Array | | [IS\_FINITE](/reference-sql/functions-reference/numeric/is_finite) | Returns `TRUE` if a value is finite, and `FALSE` otherwise. | Numeric | | [IS\_INFINITE](/reference-sql/functions-reference/numeric/is_infinite) | Returns `TRUE` if a value is infinite, and `FALSE` otherwise. | Numeric | | [JSON\_EXTRACT](/reference-sql/functions-reference/json/json-extract) | Takes an expression containing a JSON document, a JSON path expression, and an optional path syntax. If the key specified using the JSON path expression exists, `JSON_EXTRACT` returns the sub JSON document pointed by the given JSON path. Otherwise, returns `NULL`. | Semi-structured data | | [JSON\_EXTRACT\_ARRAY](/reference-sql/functions-reference/json/json-extract-array) | Takes an expression containing a JSON document, a JSON path expression, and an optional path syntax. If the key specified using the JSON path expression exists and its value is a JSON array, `JSON_EXTRACT_ARRAY` returns SQL `ARRAY(TEXT)` contains all JSON elements as raw text inside the JSON array pointed by the given JSON path. Otherwise, returns `NULL`. | Semi-structured data | | [JSON\_IS\_VALID](/reference-sql/functions-reference/json/json-is-valid) | Returns whether the input string is a valid JSON document. | Semi-structured data | | [JSON\_FORMAT](/reference-sql/functions-reference/json/json-format) | Accepts a JSON document and an optional indentation. Returns the JSON document formatted according to the specified indentation. | Semi-structured data | | [JSON\_PRETTY](/reference-sql/functions-reference/json/json-format) | Accepts a JSON document. Returns the pretty-printed JSON document. | Semi-structured data | | [JSON\_VALUE](/reference-sql/functions-reference/json/json-value) | Takes an expression containing a JSON document, removes the outermost quotes and unescapes the value, and extracts a scalar value as SQL `TEXT`, or `NULL` if a non-scalar value is selected. | Semi-structured data | | [JSON\_VALUE\_ARRAY](/reference-sql/functions-reference/json/json-value-array) | Takes an expression containing a JSON document and extracts an array of scalar values out of it to SQL `ARRAY(TEXT)`. | Semi-structured data | | [LEAST](/reference-sql/functions-reference/conditional-and-miscellaneous/greatest) | Selects the smallest value from a list of expressions. | Conditional & miscellaneous | | [LENGTH](/reference-sql/functions-reference/string/length) | Returns the length of an input string. | String | | [LIKE](/reference-sql/functions-reference/string/like) | Performs case-sensitive pattern matching of strings. | String | | [LIST\_OBJECTS](/reference-sql/functions-reference/table-valued/list-objects) | Returns an Amazon S3 object metadata and prefixes from the input URL up to the next forward slash ('/'). | Table-valued | | [LN](/reference-sql/functions-reference/numeric/ln) | Returns the natural logarithm (base e) of a number. | Numeric | | [LOCALTIMESTAMP](/reference-sql/functions-reference/date-and-time/localtimestamp) | Returns the current local timestamp in the time zone specified in the session's `time_zone` setting. | Date & time | | [LOG](/reference-sql/functions-reference/numeric/log) | Returns the logarithm of a number using a custom base, defaulting to base 10 if no base is provided. | Numeric | | [LOWER](/reference-sql/functions-reference/string/lower) | Converts the input string to lowercase characters. | String | | [LPAD](/reference-sql/functions-reference/string/lpad) | Pads a string from the left with a specified string until it reaches the specified length. | String | | [LTRIM](/reference-sql/functions-reference/string/ltrim) | Removes 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](/reference-sql/functions-reference/aggregation/max) | Calculates the maximum value of an expression across all input values. | Aggregation | | [MAX\_BY](/reference-sql/functions-reference/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](/reference-sql/functions-reference/window/max-window) | Returns the maximum value within the requested window. | Window | | [MAX\_BY](/reference-sql/functions-reference/aggregation/max-by) | Returns the value of the specified input column at the row with the maximum value in the specified value column. | Aggregation | | [MEDIAN](/reference-sql/functions-reference/aggregation/median) | Calculates the median for a given column. If number of values are even, it returns the average of the two middle values. | Aggregation | | [MIN](/reference-sql/functions-reference/aggregation/min) | Calculates the minimum value of an expression across all input values. | Aggregation | | [MIN\_BY](/reference-sql/functions-reference/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](/reference-sql/functions-reference/window/min-window) | Returns the maximum value within the requested window. | Window | | [MOD](/reference-sql/functions-reference/numeric/mod) | Calculates the remainder after dividing two values. | Numeric | | [NGRAM](/reference-sql/functions-reference/string/ngram) | Splits a text sequence into overlapping contiguous subsequences of a specified length. | String | | [NTILE](/reference-sql/functions-reference/window/ntile) | Divides an ordered set into a number of buckets. | Window | | [NULLIF](/reference-sql/functions-reference/conditional-and-miscellaneous/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](/reference-sql/functions-reference/string/octet_length) | Calculates the length of the input string in bytes. | String | | [PERCENT\_RANK](/reference-sql/functions-reference/window/percent-rank) | Calculates the relative rank of the current row within the requested window. | Window | | [PERCENTILE\_CONT](/reference-sql/functions-reference/aggregation/percentile-cont) | Calculates a percentile value for a given expression. | Aggregation | | [PI](/reference-sql/functions-reference/numeric/pi) | Returns π as a `DOUBLE PRECISION` value. | Numeric | | [POSITION](/reference-sql/functions-reference/string/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](/reference-sql/functions-reference/numeric/random) | Returns a pseudo-random unsigned value greater than 0 and less than 1 of type `DOUBLE PRECISION`. | Numeric | | [RANK](/reference-sql/functions-reference/window/rank) | Rank the current row within the requested window with gaps. | Window | | [READ\_AVRO](/reference-sql/functions-reference/table-valued/read_avro) | Returns a table with data from specified Avro files. | Table-valued | | [READ\_CSV](/reference-sql/functions-reference/table-valued/read_csv) | Returns a table with data from specified CSV file. | Table-valued | | [READ\_ICEBERG](/reference-sql/functions-reference/iceberg/read_iceberg) | Returns a table with data from specified Iceberg table. | Iceberg | | [ICEBERG\_YEAR](/reference-sql/functions-reference/iceberg/partitioning/iceberg_year) | Extracts year as years from 1970 according to Iceberg partition transforms. | Iceberg | | [ICEBERG\_MONTH](/reference-sql/functions-reference/iceberg/partitioning/iceberg_month) | Extracts month as months from 1970-01-01 according to Iceberg partition transforms. | Iceberg | | [ICEBERG\_DAY](/reference-sql/functions-reference/iceberg/partitioning/iceberg_day) | Extracts day as days from 1970-01-01 according to Iceberg partition transforms. | Iceberg | | [ICEBERG\_HOUR](/reference-sql/functions-reference/iceberg/partitioning/iceberg_hour) | Extracts hour as hours from 1970-01-01 00:00:00 according to Iceberg partition transforms. | Iceberg | | [ICEBERG\_BUCKET](/reference-sql/functions-reference/iceberg/partitioning/iceberg_bucket) | Hashes value into a bucket according to Iceberg partition transforms. | Iceberg | | [ICEBERG\_TRUNCATE](/reference-sql/functions-reference/iceberg/partitioning/iceberg_truncate) | Truncates value to a width according to Iceberg partition transforms. | Iceberg | | [READ\_PARQUET](/reference-sql/functions-reference/table-valued/read_parquet) | Returns a table with data from specified Parquet file. | Table-valued | | [REGEXP\_LIKE](/reference-sql/functions-reference/string/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\_LIKE\_ANY](/reference-sql/functions-reference/string/regexp-like-any) | Checks whether a given string matches any regular expression pattern from a specified list of patterns. Returns `FALSE` if it doesn’t match, or `TRUE` if it matches. | String | | [REGEXP\_REPLACE](/reference-sql/functions-reference/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](/reference-sql/functions-reference/string/replace) | Replaces all occurrences of the given pattern substring within the input expression with a replacement substring. | String | | [REVERSE](/reference-sql/functions-reference/string/reverse) | Returns the input string with all characters in reverse order. | String | | [ROUND](/reference-sql/functions-reference/numeric/round) | Rounds a value to a specified number of decimal places. | Numeric | | [ROW\_NUMBER](/reference-sql/functions-reference/window/row-number) | Calculate a unique, increasing row number for each row within the window. | Window | | [RPAD](/reference-sql/functions-reference/string/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](/reference-sql/functions-reference/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 | | [SESSION\_USER](/reference-sql/functions-reference/session/session-user) | Returns the name of the user running the current query. | Session | | [SPLIT\_PART](/reference-sql/functions-reference/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 | | [SQRT](/reference-sql/functions-reference/numeric/sqrt) | Calculates square root of a value. | Numeric | | [STRING\_TO\_ARRAY](/reference-sql/functions-reference/string/string-to-array) | Splits a given string by a given separator and returns the result in an array of strings. | String | | [STRPOS](/reference-sql/functions-reference/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 | | [ST\_ASBINARY](/reference-sql/functions-reference/geospatial/st_asbinary) | Converts shapes of the `GEOGRAPHY` data type to the [Well-Known Binary (WKB)](https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry#Well-known_binary) format for geographic objects. | Geospatial | | [ST\_ASEWKB](/reference-sql/functions-reference/geospatial/st_asewkb) | Converts shapes of the `GEOGRAPHY` data type to the [extended Well-Known Binary (EWKB)](https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry#Format_variations) format using Spatial Reference Identifier (SRID) 4326, which corresponds to the [WGS84](https://en.wikipedia.org/wiki/World_Geodetic_System#WGS_84) coordinate system. | Geospatial | | [ST\_ASGEOJSON](/reference-sql/functions-reference/geospatial/st_asgeojson) | Converts shapes of the `GEOGRAPHY` data type to the [GeoJSON](https://datatracker.ietf.org/doc/html/rfc7946) format. | Geospatial | | [ST\_ASTEXT](/reference-sql/functions-reference/geospatial/st_astext) | Converts shapes of the `GEOGRAPHY` data type to the [Well-Known Text (WKT)](https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry) format. | Geospatial | | [ST\_CONTAINS](/reference-sql/functions-reference/geospatial/st_contains) | The `ST_CONTAINS` function determines if one `GEOGRAPHY` object fully contains another. | Geospatial | | [ST\_COVERS](/reference-sql/functions-reference/geospatial/st_covers) | The `ST_COVERS` function determines if one `GEOGRAPHY` object fully encompasses another. | Geospatial | | [ST\_DISTANCE](/reference-sql/functions-reference/geospatial/st_distance) | The `ST_DISTANCE` function calculates the shortest distance (measured as a geodesic arc) between two `GEOGRAPHY` objects, measured in meters. | Geospatial | | [ST\_GEOGFROMGEOJSON](/reference-sql/functions-reference/geospatial/st_geogfromgeojson) | Constructs a `GEOGRAPHY` object from a [GeoJSON](https://datatracker.ietf.org/doc/html/rfc7946) string. | Geospatial | | [ST\_GEOGFROMTEXT](/reference-sql/functions-reference/geospatial/st_geogfromtext) | Constructs a `GEOGRAPHY` object from a [Well-Known Text (WKT)](https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry) string. | Geospatial | | [ST\_GEOGFROMWKB](/reference-sql/functions-reference/geospatial/st_geogfromwkb) | Constructs a `GEOGRAPHY` object from a [Well-Known Binary](https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry#Well-known_binary) (WKB) byte string. | Geospatial | | [ST\_GEOGPOINT](/reference-sql/functions-reference/geospatial/st_geogpoint) | Constructs a Point in the `GEOGRAPHY` data type created from specified longitude and latitude coordinates. | Geospatial | | [ST\_INTERSECTS](/reference-sql/functions-reference/geospatial/st_intersects) | The `ST_INTERSECTS` function determines whether two input `GEOGRAPHY` objects intersect each other. | Geospatial | | [ST\_S2CELLIDFROMPOINT](/reference-sql/functions-reference/geospatial/st_s2cellidfrompoint) | Returns the [S2 cell ID](http://s2geometry.io/devguide/s2cell_hierarchy), which uniquely identifies the region on Earth that fully contains, or covers, a single Point `GEOGRAPHY` object. | Geospatial | | [ST\_X](/reference-sql/functions-reference/geospatial/st_x) | Extracts the longitude coordinate of a `GEOGRAPHY` Point. | Geospatial | | [ST\_Y](/reference-sql/functions-reference/geospatial/st_y) | Extracts the latitude coordinate of a `GEOGRAPHY` Point. | Geospatial | | [STDDEV\_POP](/reference-sql/functions-reference/aggregation/stddev-pop) | Returns the population standard deviation value. | Aggregation | | [STDDEV\_SAMP](/reference-sql/functions-reference/aggregation/stddev-samp) | Returns the sample standard deviation value. | Aggregation | | [SUBSTRING, SUBSTR](/reference-sql/functions-reference/string/substring) | Returns a substring starting at the character indicated by the start index and including the number of characters defined. Character indexing starts at 1. | String | | [SUM](/reference-sql/functions-reference/aggregation/sum) | Calculates the sum of an expression. | Aggregation | | [SUM OVER](/reference-sql/functions-reference/window/sum-window) | Calculate the sum of the values within the requested window. | Window | | [TAN](/reference-sql/functions-reference/numeric/tan) | Calculates the tangent of a value in radians. | Numeric | | [TO\_BIN](/reference-sql/functions-reference/string/to_bin) | Converts an `INTEGER` or `BIGINT` to its binary representation using two's complement. | String | | [TO\_JSON](/reference-sql/functions-reference/json/to-json) | Converts arbitrary values to `JSON`. | Semi-structured data | | [TO\_OCT](/reference-sql/functions-reference/string/to_oct) | Converts an `INTEGER` or `BIGINT` to its octal representation. | String | | [TO\_TIMESTAMP](/reference-sql/functions-reference/date-and-time/to-timestamp) | Converts a string to timestamp with time zone. | Date & time | | [TO\_YYYYMM](/reference-sql/functions-reference/date-and-time/to-yyyymm) | Extracts year and month from a `DATE`, `TIMESTAMP`, or `TIMESTAMPTZ` value and combines them into an integer beginning with the four-digit year followed by the two-digit month. | Date & time | | [TO\_YYYYMMDD](/reference-sql/functions-reference/date-and-time/to-yyyymmdd) | Extracts year, month and day from a `DATE`, `TIMESTAMP`, or `TIMESTAMPTZ` value and combines them into an integer beginning with the four-digit year followed by the two-digit month followed by the two-digit day. | Date & time | | [TRIM](/reference-sql/functions-reference/string/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](/reference-sql/functions-reference/conditional-and-miscellaneous/try-cast) | Converts data types into other data types based on the specified parameters. Returns a `NULL` if the conversion cannot be performed. | Conditional & miscellaneous | | [TRY\_JSON\_FORMAT](/reference-sql/functions-reference/json/json-format) | Accepts a JSON document and an optional indentation. Returns the JSON document formatted according to the specified indentation. Returns `NULL` if the JSON document is malformed. | Semi-structured data | | [TRY\_JSON\_PRETTY](/reference-sql/functions-reference/json/json-format) | Accepts a JSON document. Returns the pretty-printed JSON document. Returns `NULL` if the JSON document is malformed. | Semi-structured data | | [UPPER](/reference-sql/functions-reference/string/upper) | Converts the input string to uppercase characters. | String | | [URL\_DECODE](/reference-sql/functions-reference/string/url_decode) | Decodes percent-encoded characters. | String | | [URL\_ENCODE](/reference-sql/functions-reference/string/url_encode) | Encodes characters that are not unreserved using percent encoding. | String | | [VAR\_POP](/reference-sql/functions-reference/aggregation/variance-pop) | Returns the population variance value. | Aggregation | | [VAR\_SAMP](/reference-sql/functions-reference/aggregation/variance-samp) | Returns the sample variance value. | Aggregation | | [VECTOR\_COSINE\_DISTANCE](/reference-sql/functions-reference/vector/vector-cosine-distance) | Computes the cosine distance of two vectors. | vector | | [VECTOR\_COSINE\_SIMILARITY](/reference-sql/functions-reference/vector/vector-cosine-similarity) | Computes the cosine similarity of two vectors. | vector | | [VECTOR\_EUCLIDEAN\_DISTANCE](/reference-sql/functions-reference/vector/vector-euclidean-distance) | Computes the euclidean distance of two vectors. | vector | | [VECTOR\_INNER\_PRODUCT](/reference-sql/functions-reference/vector/vector-inner-product) | Computes the inner product of two vectors. | vector | | [VECTOR\_MANHATTAN\_DISTANCE](/reference-sql/functions-reference/vector/vector-manhattan-distance) | Computes the manhattan distance of two vectors. | vector | | [VECTOR\_SQUARED\_EUCLIDEAN\_DISTANCE](/reference-sql/functions-reference/vector/vector-squared-euclidean-distance) | Computes the squared euclidean distance of two vectors. | Array | | [VECTOR\_SEARCH](/reference-sql/functions-reference/vector/vector-search) | Table‑valued function that returns up to `top_k` rows closest to a target vector using a vector index. | Table-valued | | [VERSION](/reference-sql/functions-reference/conditional-and-miscellaneous/version) | Returns the version number information for the Firebolt engine. | Conditional & miscellaneous |