> ## Documentation Index > Fetch the complete documentation index at: https://docs.e6data.com/llms.txt > Use this file to discover all available pages before exploring further. # Function inventory > Functions are grouped by purpose below. The inventory on this page is generated from the exact function registry compiled into Unified and includes canonical names, aliases, syntax ReferenceApplies to: Unified engine deployments Functions are grouped by purpose below. The inventory on this page is generated from the exact function registry compiled into Unified and includes canonical names, aliases, syntax, and argument documentation. ## Function reference | Family | Details and live examples | | - | - | | Text and regular expressions | [String functions](https://docs.e6data.com/transformation/sql/functions-string.md) | | Numeric values and rounding | [Numeric functions](https://docs.e6data.com/transformation/sql/functions-numeric.md) | | Dates, timestamps, and intervals | [Date and time functions](https://docs.e6data.com/transformation/sql/functions-datetime.md) | | Nulls and conditional values | [Conditional functions](https://docs.e6data.com/transformation/sql/functions-conditional.md) | | JSON text | [JSON functions](https://docs.e6data.com/transformation/sql/functions-json.md) | | Arrays, maps, structs, and lambdas | [Nested functions](https://docs.e6data.com/transformation/sql/functions-nested.md) | | Group reductions and statistics | [Aggregate functions](https://docs.e6data.com/transformation/sql/functions-aggregate.md) | | Ranking and neighboring rows | [Window functions](https://docs.e6data.com/transformation/sql/functions-window.md) | ## Resolution and return types Function names and aliases resolve without ASCII case sensitivity. Overload selection depends on argument types. Return types can depend on input types, decimal precision, or nested element types; use the result column schema to inspect the resolved type for a live query. A registered function name means the planner can resolve it. Execution also depends on argument types and the selected backend. This inventory is not a promise that every upstream function can execute on every backend. The family pages document tested examples and relevant restrictions. ## Unified aliases | Spelling | Equivalent | | - | - | | `nvl`, `ifnull` | `coalesce` | | `array` | `make_array` | | `rand` | `random` | | `lcase`, `ucase` | `lower`, `upper` | | `pow` | `power` | | `getfield` | `get_field` | | `transform`, `filter`, `exists` | `array_transform`, `array_filter`, `array_any_match` | | `mean` | `avg` | | `std`, `stddev_samp` | `stddev` | | `variance`, `var_samp` | `var_sample` | `struct(...)` derives field names. `map(...)` accepts alternating keys and values. `collect_list` and `collect_set` are explicitly unavailable despite similar aggregate names in other systems. SHOW FUNCTIONS is currently unavailable; use the inventory below. ## Registered functions The inventory lists names, aliases, families, signatures, descriptions, and arguments. Internal arithmetic helpers are excluded. Descriptive function metadata comes from the engine's Apache-2.0 DataFusion dependencies; Unified-specific restrictions take precedence. ### Math Functions #### abs Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} abs(numeric_expression) ``` Returns the absolute value of a number. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### acos Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} acos(numeric_expression) ``` Returns the arc cosine or inverse cosine of a number. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### acosh Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} acosh(numeric_expression) ``` Returns the area hyperbolic cosine or inverse hyperbolic cosine of a number. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### asin Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} asin(numeric_expression) ``` Returns the arc sine or inverse sine of a number. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### asinh Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} asinh(numeric_expression) ``` Returns the area hyperbolic sine or inverse hyperbolic sine of a number. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### atan Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} atan(numeric_expression) ``` Returns the arc tangent or inverse tangent of a number. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### atan2 Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} atan2(expression_y, expression_x) ``` Returns the arc tangent or inverse tangent of `expression_y / expression_x`. * **expression\_y**: First numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. * **expression\_x**: Second numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. #### atanh Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} atanh(numeric_expression) ``` Returns the area hyperbolic tangent or inverse hyperbolic tangent of a number. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### cbrt Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} cbrt(numeric_expression) ``` Returns the cube root of a number. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### ceil Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} ceil(numeric_expression) ``` Returns the nearest integer greater than or equal to a number. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### cos Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} cos(numeric_expression) ``` Returns the cosine of a number. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### cosh Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} cosh(numeric_expression) ``` Returns the hyperbolic cosine of a number. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### cot Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} cot(numeric_expression) ``` Returns the cotangent of a number. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### degrees Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} degrees(numeric_expression) ``` Converts radians to degrees. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### exp Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} exp(numeric_expression) ``` Returns the base-e exponential of a number. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### factorial Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} factorial(numeric_expression) ``` Factorial of a non-negative integer. Errors if the argument is negative or the result overflows. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### floor Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} floor(numeric_expression) ``` Returns the nearest integer less than or equal to a number. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### gcd Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} gcd(expression_x, expression_y) ``` Returns the greatest common divisor of `expression_x` and `expression_y`. Returns 0 if both inputs are zero. * **expression\_x**: First numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. * **expression\_y**: Second numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### isnan Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} isnan(numeric_expression) ``` Returns true if a given number is +NaN or -NaN otherwise returns false. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### iszero Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} iszero(numeric_expression) ``` Returns true if a given number is +0.0 or -0.0 otherwise returns false. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### lcm Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} lcm(expression_x, expression_y) ``` Returns the least common multiple of `expression_x` and `expression_y`. Returns 0 if either input is zero. * **expression\_x**: First numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. * **expression\_y**: Second numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### ln Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} ln(numeric_expression) ``` Returns the natural logarithm of a number. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### log Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} log(base, numeric_expression) log(numeric_expression) ``` Returns the base-x logarithm of a number. Can either provide a specified base, or if omitted then takes the base-10 of a number. * **base**: Base numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### log10 Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} log10(numeric_expression) ``` Returns the base-10 logarithm of a number. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### log2 Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} log2(numeric_expression) ``` Returns the base-2 logarithm of a number. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### nanvl Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} nanvl(expression_x, expression_y) ``` Returns the first argument if it's not *NaN*. Returns the second argument otherwise. * **expression\_x**: Numeric expression to return if it's not *NaN*. Can be a constant, column, or function, and any combination of arithmetic operators. * **expression\_y**: Numeric expression to return if the first expression is *NaN*. Can be a constant, column, or function, and any combination of arithmetic operators. #### pi Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} pi() ``` Returns an approximate value of π. #### power Kind: Scalar. Aliases: `pow`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} power(base, exponent) ``` Returns a base expression raised to the power of an exponent. * **base**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. * **exponent**: Exponent numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### radians Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} radians(numeric_expression) ``` Converts degrees to radians. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### random Kind: Scalar. Aliases: `rand`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} random() ``` Returns a random float value in the range \[0, 1). The random seed is unique to each row. #### round Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} round(numeric_expression[, decimal_places]) ``` Rounds a number to the nearest integer. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. * **decimal\_places**: Optional. The number of decimal places to round to. Defaults to 0. #### signum Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} signum(numeric_expression) ``` Returns the sign of a number. Negative numbers return `-1`. Zero and positive numbers return `1`. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### sin Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} sin(numeric_expression) ``` Returns the sine of a number. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### sinh Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} sinh(numeric_expression) ``` Returns the hyperbolic sine of a number. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### sqrt Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} sqrt(numeric_expression) ``` Returns the square root of a number. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### tan Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} tan(numeric_expression) ``` Returns the tangent of a number. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### tanh Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} tanh(numeric_expression) ``` Returns the hyperbolic tangent of a number. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### trunc Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} trunc(numeric_expression[, decimal_places]) ``` Truncates a number to a whole number or truncated to the specified decimal places. * **numeric\_expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. * **decimal\_places**: Optional. The number of decimal places to truncate to. Defaults to 0 (truncate to a whole number). If `decimal_places` is a positive integer, truncates digits to the right of the decimal point. If `decimal_places` is a negative integer, replaces digits to the left of the decimal point with `0`. ### Approximate Functions #### approx\_distinct Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} approx_distinct(expression) ``` Returns the approximate number of distinct input values calculated using the HyperLogLog algorithm. * **expression**: The expression to operate on. Can be a constant, column, or function, and any combination of operators. #### approx\_median Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} approx_median(expression) ``` Returns the approximate median (50th percentile) of input values. It is an alias of `approx_percentile_cont(0.5) WITHIN GROUP (ORDER BY x)`. * **expression**: The expression to operate on. Can be a constant, column, or function, and any combination of operators. #### approx\_percentile\_cont Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} approx_percentile_cont(percentile [, centroids]) WITHIN GROUP (ORDER BY expression) ``` Returns the approximate percentile of input values using the t-digest algorithm. * **expression**: The expression to operate on. Can be a constant, column, or function, and any combination of operators. * **percentile**: Percentile to compute. Must be a float value between 0 and 1 (inclusive). * **centroids**: Number of centroids to use in the t-digest algorithm. *Default is 100*. A higher number results in more accurate approximation but requires more memory. #### approx\_percentile\_cont\_with\_weight Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} approx_percentile_cont_with_weight(weight, percentile [, centroids]) WITHIN GROUP (ORDER BY expression) ``` Returns the weighted approximate percentile of input values using the t-digest algorithm. * **expression**: The expression to operate on. Can be a constant, column, or function, and any combination of operators. * **weight**: Expression to use as weight. Can be a constant, column, or function, and any combination of arithmetic operators. * **percentile**: Percentile to compute. Must be a float value between 0 and 1 (inclusive). * **centroids**: Number of centroids to use in the t-digest algorithm. *Default is 100*. A higher number results in more accurate approximation but requires more memory. ### General Functions #### array\_agg Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_agg(expression [ORDER BY expression]) ``` Returns an array created from the expression elements. If ordering is required, elements are inserted in the specified order. This aggregation function can only mix DISTINCT and ORDER BY if the ordering expression is exactly the same as the argument expression. * **expression**: The expression to operate on. Can be a constant, column, or function, and any combination of operators. #### avg Kind: Aggregate. Aliases: `mean`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} avg(expression) ``` Returns the average of numeric values in the specified column. * **expression**: The expression to operate on. Can be a constant, column, or function, and any combination of operators. #### bit\_and Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} bit_and(expression) ``` Computes the bitwise AND of all non-null input values. * **expression**: Integer expression to operate on. Can be a constant, column, or function, and any combination of operators. #### bit\_or Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} bit_or(expression) ``` Computes the bitwise OR of all non-null input values. * **expression**: Integer expression to operate on. Can be a constant, column, or function, and any combination of operators. #### bit\_xor Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} bit_xor(expression) ``` Computes the bitwise exclusive OR of all non-null input values. * **expression**: Integer expression to operate on. Can be a constant, column, or function, and any combination of operators. #### bool\_and Kind: Aggregate. Aliases: `every`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} bool_and(expression) ``` Returns true if all non-null input values are true, otherwise false. * **expression**: The expression to operate on. Can be a constant, column, or function, and any combination of operators. #### bool\_or Kind: Aggregate. Aliases: `any`, `some`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} bool_and(expression) ``` Returns true if all non-null input values are true, otherwise false. * **expression**: The expression to operate on. Can be a constant, column, or function, and any combination of operators. #### count Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} count(expression) ``` Returns the number of non-null values in the specified column. To include null values in the total count, use `count(*)`. * **expression**: The expression to operate on. Can be a constant, column, or function, and any combination of operators. #### first\_value Kind: Aggregate. Aliases: `first`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} first_value(expression [ORDER BY expression]) ``` Returns the first element in an aggregation group according to the requested ordering. If no ordering is given, returns an arbitrary element from the group. * **expression**: The expression to operate on. Can be a constant, column, or function, and any combination of operators. #### grouping Kind: Aggregate. Aliases: `grouping_id`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} grouping(expression) ``` Returns 1 if the data is aggregated across the specified column, or 0 if it is not aggregated in the result set. * **expression**: Expression to evaluate whether data is aggregated across the specified column. Can be a constant, column, or function. #### last\_value Kind: Aggregate. Aliases: `last`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} last_value(expression [ORDER BY expression]) ``` Returns the last element in an aggregation group according to the requested ordering. If no ordering is given, returns an arbitrary element from the group. * **expression**: The expression to operate on. Can be a constant, column, or function, and any combination of operators. #### max Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} max(expression) ``` Returns the maximum value in the specified column. * **expression**: The expression to operate on. Can be a constant, column, or function, and any combination of operators. #### median Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} median(expression) ``` Returns the median value in the specified column. * **expression**: The expression to operate on. Can be a constant, column, or function, and any combination of operators. #### min Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} min(expression) ``` Returns the minimum value in the specified column. * **expression**: The expression to operate on. Can be a constant, column, or function, and any combination of operators. #### percentile\_cont Kind: Aggregate. Aliases: `quantile_cont`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} percentile_cont(percentile) WITHIN GROUP (ORDER BY expression) ``` Returns the exact percentile of input values, interpolating between values if needed. * **expression**: The expression to operate on. Can be a constant, column, or function, and any combination of operators. * **percentile**: Percentile to compute. Must be a float value between 0 and 1 (inclusive). #### string\_agg Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} string_agg([DISTINCT] expression, delimiter [ORDER BY expression]) ``` Concatenates the values of string expressions and places separator values between them. If ordering is required, strings are concatenated in the specified order. This aggregation function can only mix DISTINCT and ORDER BY if the ordering expression is exactly the same as the first argument expression. * **expression**: The string expression to concatenate. Can be a column or any valid string expression. * **delimiter**: A literal string used as a separator between the concatenated values. #### sum Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} sum(expression) ``` Returns the sum of all values in the specified column. * **expression**: The expression to operate on. Can be a constant, column, or function, and any combination of operators. #### var Kind: Aggregate. Aliases: `var_samp`, `var_sample`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} var(expression) ``` Returns the statistical sample variance of a set of numbers. * **expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. #### var\_pop Kind: Aggregate. Aliases: `var_population`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} var_pop(expression) ``` Returns the statistical population variance of a set of numbers. * **expression**: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators. ### Array Functions #### array\_any\_match Kind: Higher order. Aliases: `any_match`, `exists`, `list_any_match`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} any_match(array, predicate) ``` Returns whether any elements of an array match the given predicate. Returns true if one or more elements match, false if none match (including empty arrays), and null if the predicate returns null for some elements and false for all others. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **predicate**: Lambda predicate that returns a boolean #### array\_any\_value Kind: Scalar. Aliases: `list_any_value`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_any_value(array) ``` Returns the first non-null element in the array. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. #### array\_append Kind: Scalar. Aliases: `array_push_back`, `list_append`, `list_push_back`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_append(array, element) ``` Appends an element to the end of an array. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **element**: Element to append to the array. #### array\_compact Kind: Scalar. Aliases: `list_compact`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_compact(array) ``` Removes null values from the array. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. #### array\_concat Kind: Scalar. Aliases: `array_cat`, `list_cat`, `list_concat`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_concat(array[, ..., array_n]) ``` Concatenates arrays. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **array\_n**: Subsequent array column or literal array to concatenate. #### array\_dims Kind: Scalar. Aliases: `list_dims`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_dims(array) ``` Returns an array of the array's dimensions. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. #### array\_distance Kind: Scalar. Aliases: `list_distance`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_distance(array1, array2) ``` Returns the Euclidean distance between two input arrays of equal length. * **array1**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **array2**: Array expression. Can be a constant, column, or function, and any combination of array operators. #### array\_distinct Kind: Scalar. Aliases: `list_distinct`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_distinct(array) ``` Returns distinct values from the array after removing duplicates. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. #### array\_element Kind: Scalar. Aliases: `array_extract`, `list_element`, `list_extract`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_element(array, index) ``` Extracts the element with the index n from the array. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **index**: Index to extract the element from the array. #### array\_except Kind: Scalar. Aliases: `list_except`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_except(array1, array2) ``` Returns an array of the elements that appear in the first array but not in the second. * **array1**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **array2**: Array expression. Can be a constant, column, or function, and any combination of array operators. #### array\_filter Kind: Higher order. Aliases: `filter`, `list_filter`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_filter(array, x -> x > 2) ``` filters the values of an array using a boolean lambda * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **lambda**: Lambda that returns a boolean. Elements for which the lambda returns true are kept. #### array\_has Kind: Scalar. Aliases: `array_contains`, `list_contains`, `list_has`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_has(array, element) ``` Returns true if the array contains the element. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **element**: Scalar or Array expression. Can be a constant, column, or function, and any combination of array operators. #### array\_has\_all Kind: Scalar. Aliases: `list_has_all`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_has_all(array, sub-array) ``` Returns true if all elements of sub-array exist in array. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **sub-array**: Array expression. Can be a constant, column, or function, and any combination of array operators. #### array\_has\_any Kind: Scalar. Aliases: `arrays_overlap`, `list_has_any`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_has_any(array1, array2) ``` Returns true if the arrays have any elements in common. * **array1**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **array2**: Array expression. Can be a constant, column, or function, and any combination of array operators. #### array\_intersect Kind: Scalar. Aliases: `list_intersect`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_intersect(array1, array2) ``` Returns an array of elements in the intersection of array1 and array2. * **array1**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **array2**: Array expression. Can be a constant, column, or function, and any combination of array operators. #### array\_length Kind: Scalar. Aliases: `list_length`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_length(array, dimension) ``` Returns the length of the array dimension. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **dimension**: Array dimension. #### array\_max Kind: Scalar. Aliases: `list_max`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_max(array) ``` Returns the maximum value in the array. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. #### array\_min Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_min(array) ``` Returns the minimum value in the array. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. #### array\_ndims Kind: Scalar. Aliases: `list_ndims`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_ndims(array, element) ``` Returns the number of dimensions of the array. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **element**: Array element. #### array\_normalize Kind: Scalar. Aliases: `list_normalize`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_normalize(array) ``` Returns the L2-normalized vector for the input numeric array, computed as `array[i] / sqrt(sum(array[i]^2))` per element. Returns NULL if the input is NULL, contains NULL elements, or has zero magnitude (all elements are zero). Returns an empty array for an empty input array. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. #### array\_pop\_back Kind: Scalar. Aliases: `list_pop_back`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_pop_back(array) ``` Returns the array without the last element. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. #### array\_pop\_front Kind: Scalar. Aliases: `list_pop_front`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_pop_front(array) ``` Returns the array without the first element. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. #### array\_position Kind: Scalar. Aliases: `array_indexof`, `list_indexof`, `list_position`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_position(array, element) array_position(array, element, index) ``` Returns the position of the first occurrence of the specified element in the array, or NULL if not found. Comparisons are done using `IS DISTINCT FROM` semantics, so NULL is considered to match NULL. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **element**: Element to search for in the array. * **index**: Index at which to start searching (1-indexed). #### array\_positions Kind: Scalar. Aliases: `list_positions`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_positions(array, element) ``` Searches for an element in the array, returns all occurrences. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **element**: Element to search for in the array. #### array\_prepend Kind: Scalar. Aliases: `array_push_front`, `list_prepend`, `list_push_front`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_prepend(element, array) ``` Prepends an element to the beginning of an array. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **element**: Element to prepend to the array. #### array\_remove Kind: Scalar. Aliases: `list_remove`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_remove(array, element) ``` Removes the first element from the array equal to the given value. NULL elements already in the array are preserved when removing a non-NULL value. If `element` evaluates to NULL, the result is NULL rather than removing NULL entries. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **element**: Element to be removed from the array. #### array\_remove\_all Kind: Scalar. Aliases: `list_remove_all`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_remove_all(array, element) ``` Removes all elements from the array equal to the given value. NULL elements already in the array are preserved when removing a non-NULL value. If `element` evaluates to NULL, the result is NULL rather than removing NULL entries. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **element**: Element to be removed from the array. #### array\_remove\_n Kind: Scalar. Aliases: `list_remove_n`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_remove_n(array, element, max) ``` Removes the first `max` elements from the array equal to the given value. NULL elements already in the array are preserved when removing a non-NULL value. If `element` evaluates to NULL, the result is NULL rather than removing NULL entries. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **element**: Element to be removed from the array. * **max**: Number of first occurrences to remove. #### array\_repeat Kind: Scalar. Aliases: `list_repeat`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_repeat(element, count) ``` Returns an array containing element `count` times. * **element**: Element expression. Can be a constant, column, or function, and any combination of array operators. * **count**: Value of how many times to repeat the element. #### array\_replace Kind: Scalar. Aliases: `list_replace`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_replace(array, from, to) ``` Replaces the first occurrence of the specified element with another specified element. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **from**: Initial element. * **to**: Final element. #### array\_replace\_all Kind: Scalar. Aliases: `list_replace_all`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_replace_all(array, from, to) ``` Replaces all occurrences of the specified element with another specified element. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **from**: Initial element. * **to**: Final element. #### array\_replace\_n Kind: Scalar. Aliases: `list_replace_n`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_replace_n(array, from, to, max) ``` Replaces the first `max` occurrences of the specified element with another specified element. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **from**: Initial element. * **to**: Final element. * **max**: Number of first occurrences to replace. #### array\_resize Kind: Scalar. Aliases: `list_resize`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_resize(array, size, value) ``` Resizes the list to contain size elements. Initializes new elements with value or empty if value is not set. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **size**: New size of given array. * **value**: Defines new elements' value or empty if value is not set. #### array\_reverse Kind: Scalar. Aliases: `list_reverse`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_reverse(array) ``` Returns the array with the order of the elements reversed. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. #### array\_slice Kind: Scalar. Aliases: `list_slice`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_slice(array, begin, end) ``` Returns a slice of the array based on 1-indexed start and end positions. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **begin**: Index of the first element. If negative, it counts backward from the end of the array. * **end**: Index of the last element. If negative, it counts backward from the end of the array. * **stride**: Stride of the array slice. The default is 1. #### array\_sort Kind: Scalar. Aliases: `list_sort`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_sort(array, desc, nulls_first) ``` Sort array. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **desc**: Whether to sort in ascending (`ASC`) or descending (`DESC`) order. The default is `ASC`. * **nulls\_first**: Whether to sort nulls first (`NULLS FIRST`) or last (`NULLS LAST`). The default is `NULLS FIRST`. #### array\_to\_string Kind: Scalar. Aliases: `array_join`, `list_join`, `list_to_string`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_to_string(array, delimiter[, null_string]) ``` Converts each element to its text representation. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **delimiter**: Array element separator. * **null\_string**: Optional. String to use for null values in the output. If not provided, nulls will be omitted. #### array\_transform Kind: Higher order. Aliases: `list_transform`, `transform`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_transform(array, x -> x*2) ``` transforms the values of an array * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **lambda**: Lambda #### array\_union Kind: Scalar. Aliases: `list_union`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} array_union(array1, array2) ``` Returns an array of elements that are present in both arrays (all elements from both arrays) without duplicates. * **array1**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **array2**: Array expression. Can be a constant, column, or function, and any combination of array operators. #### arrays\_zip Kind: Scalar. Aliases: `list_zip`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} arrays_zip(array1[, ..., array_n]) ``` Returns an array of structs created by combining the elements of each input array at the same index. If the arrays have different lengths, shorter arrays are padded with NULLs. * **array1**: First array expression. * **array\_n**: Optional additional array expressions. #### cardinality Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} cardinality(array) ``` Returns the total number of elements in the array. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. #### cosine\_distance Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} cosine_distance(array1, array2) ``` Returns the cosine distance between two input arrays of equal length. The cosine distance is defined as 1 - cosine\_similarity, i.e. `1 - dot(a,b) / (||a|| * ||b||)`. Returns NULL if either array is NULL or contains only zeros. * **array1**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **array2**: Array expression. Can be a constant, column, or function, and any combination of array operators. #### empty Kind: Scalar. Aliases: `array_empty`, `list_empty`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} empty(array) ``` Returns 1 for an empty array or 0 for a non-empty array. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. #### flatten Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} flatten(array) ``` Converts an array of arrays to a flat array. * Applies to any depth of nested arrays * Does not change arrays that are already flat The flattened array contains all the elements from all source arrays. * **array**: Array expression. Can be a constant, column, or function, and any combination of array operators. #### generate\_series Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} generate_series(stop) generate_series(start, stop[, step]) ``` Similar to the range function, but it includes the upper bound. * **start**: Start of the series. Ints, timestamps, dates or string types that can be coerced to Date32 are supported. * **end**: End of the series (included). Type must be the same as start. * **step**: Increase by step (can not be 0). Steps less than a day are supported only for timestamp ranges. #### inner\_product Kind: Scalar. Aliases: `dot_product`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} inner_product(array1, array2) ``` Returns the inner product (dot product) of two input arrays of equal length, computed as `sum(array1[i] * array2[i])`. Returns NULL if either array is NULL or contains NULL elements. Returns 0.0 for two empty arrays. * **array1**: Array expression. Can be a constant, column, or function, and any combination of array operators. * **array2**: Array expression. Can be a constant, column, or function, and any combination of array operators. #### make\_array Kind: Scalar. Aliases: `array`, `make_list`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} make_array(expression1[, ..., expression_n]) ``` Returns an array using the specified input expressions. * **expression\_n**: Expression to include in the output array. Can be a constant, column, or function, and any combination of arithmetic or string operators. #### range Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} range(stop) range(start, stop[, step]) ``` Returns an Arrow array between start and stop with step. The range start..end contains all values with start \<= x \< end. It is empty if start >= end. Step cannot be 0. * **start**: Start of the range. Ints, timestamps, dates or string types that can be coerced to Date32 are supported. * **end**: End of the range (not included). Type must be the same as start. * **step**: Increase by step (cannot be 0). Steps less than a day are supported only for timestamp ranges. #### string\_to\_array Kind: Scalar. Aliases: `string_to_list`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} string_to_array(str, delimiter[, null_str]) ``` Splits a string into an array of substrings based on a delimiter. Any substrings matching the optional `null_str` argument are replaced with NULL. * **str**: String expression to split. * **delimiter**: Delimiter string to split on. * **null\_str**: Substring values to be replaced with `NULL`. ### Other Functions #### arrow\_cast Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} arrow_cast(expression, datatype) ``` Casts a value to a specific Arrow data type. * **expression**: Expression to cast. The expression can be a constant, column, or function, and any combination of operators. * **datatype**: [Arrow data type](https://docs.rs/arrow/latest/arrow/datatypes/enum.DataType.html) name to cast to, as a string. The format is the same as that returned by \[`arrow_typeof`] #### arrow\_field Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} arrow_field(expression) ``` Returns a struct containing the Arrow field information of the expression, including name, data type, nullability, and metadata. * **expression**: Expression to evaluate. The expression can be a constant, column, or function, and any combination of operators. #### arrow\_metadata Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} arrow_metadata(expression[, key]) ``` Returns the metadata of the input expression. If a key is provided, returns the value for that key. If no key is provided, returns a Map of all metadata. * **expression**: The expression to retrieve metadata from. Can be a column or other expression. * **key**: Optional. The specific metadata key to retrieve. #### arrow\_try\_cast Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} arrow_try_cast(expression, datatype) ``` Casts a value to a specific Arrow data type, returning NULL if the cast fails. * **expression**: Expression to cast. The expression can be a constant, column, or function, and any combination of operators. * **datatype**: [Arrow data type](https://docs.rs/arrow/latest/arrow/datatypes/enum.DataType.html) name to cast to, as a string. The format is the same as that returned by \[`arrow_typeof`] #### arrow\_typeof Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} arrow_typeof(expression) ``` Returns the name of the underlying [Arrow data type](https://docs.rs/arrow/latest/arrow/datatypes/enum.DataType.html) of the expression. * **expression**: Expression to evaluate. The expression can be a constant, column, or function, and any combination of operators. #### cast\_to\_type Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} cast_to_type(expression, reference) ``` Casts the first argument to the data type of the second argument. Only the type of the second argument is used; its value is ignored. * **expression**: The expression to cast. It can be a constant, column, or function, and any combination of operators. * **reference**: Reference expression whose data type determines the target cast type. The value is ignored. #### get\_field Kind: Scalar. Aliases: `getfield`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} get_field(expression, field_name[, field_name2, ...]) ``` Returns a field within a map or a struct with the given key. Supports nested field access by providing multiple field names. Note: most users invoke `get_field` indirectly via field access syntax such as `my_struct_col['field_name']` which results in a call to `get_field(my_struct_col, 'field_name')`. Nested access like `my_struct['a']['b']` is optimized to a single call: `get_field(my_struct, 'a', 'b')`. * **expression**: The map or struct to retrieve a field from. * **field\_name**: The field name(s) to access, in order for nested access. Must evaluate to strings. #### try\_cast\_to\_type Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} try_cast_to_type(expression, reference) ``` Casts the first argument to the data type of the second argument, returning NULL if the cast fails. Only the type of the second argument is used; its value is ignored. * **expression**: The expression to cast. It can be a constant, column, or function, and any combination of operators. * **reference**: Reference expression whose data type determines the target cast type. The value is ignored. #### version Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} version() ``` Returns the version of DataFusion. #### with\_metadata Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} with_metadata(expression, key1, value1[, key2, value2, ...]) ``` Attaches Arrow field metadata (key/value pairs) to the input expression. Keys must be non-empty constant strings and values must be constant strings (empty values are allowed). Existing metadata on the input field is preserved; new keys overwrite on collision. This is the inverse of `arrow_metadata`. * **expression**: The expression whose output Arrow field should be annotated. Values flow through unchanged. * **key**: Metadata key. Must be a non-empty constant string literal. * **value**: Metadata value. Must be a constant string literal (may be empty). ### String Functions #### ascii Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} ascii(str) ``` Returns the first Unicode scalar value of a string. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. #### bit\_length Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} bit_length(str) ``` Returns the bit length of a string. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. #### btrim Kind: Scalar. Aliases: `trim`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} btrim(str[, trim_str]) ``` Trims the specified trim string from the start and end of a string. If no trim string is provided, all spaces are removed from the start and end of the input string. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **trim\_str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. *Default is a space.* #### character\_length Kind: Scalar. Aliases: `char_length`, `length`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} character_length(str) ``` Returns the number of characters in a string. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. #### chr Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} chr(expression) ``` Returns a string containing the character with the specified Unicode scalar value. * **expression**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. #### concat Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} concat(str[, ..., str_n]) ``` Concatenates multiple strings together. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **str\_n**: Subsequent string expressions to concatenate. #### concat\_ws Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} concat_ws(separator, str[, ..., str_n]) ``` Concatenates multiple strings together with a specified separator. * **separator**: Separator to insert between concatenated strings. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **str\_n**: Subsequent string expressions to concatenate. #### contains Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} contains(str, search_str) ``` Return true if search\_str is found within string (case-sensitive). * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **search\_str**: The string to search for in str. #### ends\_with Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} ends_with(str, substr) ``` Tests if a string ends with a substring. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **substr**: Substring to test for. #### find\_in\_set Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} find_in_set(str, strlist) ``` Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. * **str**: String expression to find in strlist. * **strlist**: A string list is a string composed of substrings separated by , characters. #### initcap Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} initcap(str) ``` Capitalizes the first character in each word in the input string. Words are delimited by non-alphanumeric characters. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. #### left Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} left(str, n) ``` Returns a specified number of characters from the left side of a string. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **n**: Number of characters to return. #### levenshtein Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} levenshtein(str1, str2) ``` Returns the [`Levenshtein distance`](https://en.wikipedia.org/wiki/Levenshtein_distance) between the two given strings. * **str1**: String expression to compute Levenshtein distance with str2. * **str2**: String expression to compute Levenshtein distance with str1. #### lower Kind: Scalar. Aliases: `lcase`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} lower(str) ``` Converts a string to lower-case. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. #### lpad Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} lpad(str, n[, padding_str]) ``` Pads the left side of a string with another string to a specified string length. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **n**: String length to pad to. If the input string is longer than this length, it is truncated (on the right). * **padding\_str**: Optional string expression to pad with. Can be a constant, column, or function, and any combination of string operators. *Default is a space.* #### ltrim Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} ltrim(str[, trim_str]) ``` Trims the specified trim string from the beginning of a string. If no trim string is provided, spaces are removed from the start of the input string. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **trim\_str**: String expression to trim from the beginning of the input string. Can be a constant, column, or function, and any combination of arithmetic operators. *Default is a space.* #### octet\_length Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} octet_length(str) ``` Returns the length of a string in bytes. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. #### overlay Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} overlay(str PLACING substr FROM pos [FOR count]) ``` Returns the string which is replaced by another string from the specified position and specified count length. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **substr**: Substring to replace in str. * **pos**: The start position to start the replace in str. * **count**: The count of characters to be replaced from start position of str. If not specified, will use substr length instead. #### repeat Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} repeat(str, n) ``` Returns a string with an input string repeated a specified number. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **n**: Number of times to repeat the input string. #### replace Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} replace(str, substr, replacement) ``` Replaces all occurrences of a specified substring in a string with a new substring. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **substr**: Substring expression to replace in the input string. Substring expression to operate on. Can be a constant, column, or function, and any combination of operators. * **replacement**: Replacement substring expression to operate on. Can be a constant, column, or function, and any combination of operators. #### reverse Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} reverse(str) ``` Reverses the character order of a string. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. #### right Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} right(str, n) ``` Returns a specified number of characters from the right side of a string. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **n**: Number of characters to return. #### rpad Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} rpad(str, n[, padding_str]) ``` Pads the right side of a string with another string to a specified string length. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **n**: String length to pad to. If the input string is longer than this length, it is truncated. * **padding\_str**: String expression to pad with. Can be a constant, column, or function, and any combination of string operators. *Default is a space.* #### rtrim Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} rtrim(str[, trim_str]) ``` Trims the specified trim string from the end of a string. If no trim string is provided, all spaces are removed from the end of the input string. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **trim\_str**: String expression to trim from the end of the input string. Can be a constant, column, or function, and any combination of arithmetic operators. *Default is a space.* #### split\_part Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} split_part(str, delimiter, pos) ``` Splits a string based on a specified delimiter and returns the substring in the specified position. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **delimiter**: String or character to split on. * **pos**: Position of the part to return (counting from 1). Negative values count backward from the end of the string. #### starts\_with Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} starts_with(str, substr) ``` Tests if a string starts with a substring. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **substr**: Substring to test for. #### strpos Kind: Scalar. Aliases: `instr`, `position`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} strpos(str, substr) ``` Returns the starting position of a specified substring in a string. Positions begin at 1. If the substring does not exist in the string, the function returns 0. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **substr**: Substring expression to search for. #### substr Kind: Scalar. Aliases: `substring`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} substr(str, start_pos[, length]) ``` Extracts a substring of a specified number of characters from a specific starting position in a string. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **start\_pos**: Character position to start the substring at. The first character in the string has a position of 1. If the start position is less than 1, it is treated as if it is before the start of the string and the (absolute) number of characters before position 1 is subtracted from `length` (if given). For example, `substr('abc', -3, 6)` returns `'ab'`. * **length**: Number of characters to extract. If not specified, returns the rest of the string after the start position. #### substr\_index Kind: Scalar. Aliases: `substring_index`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} substr_index(str, delim, count) ``` Returns the substring from str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **delim**: The string to find in str to split str. * **count**: The number of times to search for the delimiter. Can be either a positive or negative number. #### to\_hex Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} to_hex(int) ``` Converts an integer to a hexadecimal string. * **int**: Integer expression to operate on. Can be a constant, column, or function, and any combination of operators. #### translate Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} translate(str, from, to) ``` Performs character-wise substitution based on a mapping. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **from**: The characters to be replaced. * **to**: The characters to replace them with. Each character in **from** that is found in **str** is replaced by the character at the same index in **to**. Any characters in **from** that don't have a corresponding character in **to** are removed. If a character appears more than once in **from**, the first occurrence determines the mapping. #### upper Kind: Scalar. Aliases: `ucase`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} upper(str) ``` Converts a string to upper-case. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. #### uuid Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} uuid() ``` Returns [`UUID v4`](https://en.wikipedia.org/wiki/Universally_unique_identifier#Version_4_%28random%29) string value which is unique per row. ### Conditional Functions #### coalesce Kind: Scalar. Aliases: `ifnull`, `nvl`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} coalesce(expression1[, ..., expression_n]) ``` Returns the first of its arguments that is not *null*. Returns *null* if all arguments are *null*. This function is often used to substitute a default value for *null* values. * **expression1, expression\_n**: Expression to use if previous expressions are *null*. Can be a constant, column, or function, and any combination of arithmetic operators. Pass as many expression arguments as necessary. #### greatest Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} greatest(expression1[, ..., expression_n]) ``` Returns the greatest value in a list of expressions. Returns *null* if all expressions are *null*. * **expression1, expression\_n**: Expressions to compare and return the greatest value.. Can be a constant, column, or function, and any combination of arithmetic operators. Pass as many expression arguments as necessary. #### least Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} least(expression1[, ..., expression_n]) ``` Returns the smallest value in a list of expressions. Returns *null* if all expressions are *null*. * **expression1, expression\_n**: Expressions to compare and return the smallest value. Can be a constant, column, or function, and any combination of arithmetic operators. Pass as many expression arguments as necessary. #### nullif Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} nullif(expression1, expression2) ``` Returns *null* if *expression1* equals *expression2*; otherwise it returns *expression1*. This can be used to perform the inverse operation of [`coalesce`](#coalesce). * **expression1**: Expression to compare and return if equal to expression2. Can be a constant, column, or function, and any combination of operators. * **expression2**: Expression to compare to expression1. Can be a constant, column, or function, and any combination of operators. #### nvl Kind: Scalar. Aliases: `ifnull`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} nvl(expression1, expression2) ``` Returns *expression2* if *expression1* is NULL otherwise it returns *expression1* and *expression2* is not evaluated. This function can be used to substitute a default value for NULL values. * **expression1**: Expression to return if not null. Can be a constant, column, or function, and any combination of operators. * **expression2**: Expression to return if expr1 is null. Can be a constant, column, or function, and any combination of operators. #### nvl2 Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} nvl2(expression1, expression2, expression3) ``` Returns *expression2* if *expression1* is not NULL; otherwise it returns *expression3*. * **expression1**: Expression to test for null. Can be a constant, column, or function, and any combination of operators. * **expression2**: Expression to return if expr1 is not null. Can be a constant, column, or function, and any combination of operators. * **expression3**: Expression to return if expr1 is null. Can be a constant, column, or function, and any combination of operators. ### Statistical Functions #### corr Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} corr(expression1, expression2) ``` Returns the coefficient of correlation between two numeric values. * **expression1**: First expression to operate on. Can be a constant, column, or function, and any combination of operators. * **expression2**: Second expression to operate on. Can be a constant, column, or function, and any combination of operators. #### covar\_pop Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} covar_samp(expression1, expression2) ``` Returns the sample covariance of a set of number pairs. * **expression1**: First expression to operate on. Can be a constant, column, or function, and any combination of operators. * **expression2**: Second expression to operate on. Can be a constant, column, or function, and any combination of operators. #### covar\_samp Kind: Aggregate. Aliases: `covar`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} covar_samp(expression1, expression2) ``` Returns the sample covariance of a set of number pairs. * **expression1**: First expression to operate on. Can be a constant, column, or function, and any combination of operators. * **expression2**: Second expression to operate on. Can be a constant, column, or function, and any combination of operators. #### nth\_value Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} nth_value(expression, n ORDER BY expression) ``` Returns the nth value in a group of values. * **expression**: The column or expression to retrieve the nth value from. * **n**: The position (nth) of the value to retrieve, based on the ordering. #### regr\_avgx Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} regr_avgx(expression_y, expression_x) ``` Computes the average of the independent variable (input) expression\_x for the non-null paired data points. * **expression\_y**: Dependent variable expression to operate on. Can be a constant, column, or function, and any combination of operators. * **expression\_x**: Independent variable expression to operate on. Can be a constant, column, or function, and any combination of operators. #### regr\_avgy Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} regr_avgy(expression_y, expression_x) ``` Computes the average of the dependent variable (output) expression\_y for the non-null paired data points. * **expression\_y**: Dependent variable expression to operate on. Can be a constant, column, or function, and any combination of operators. * **expression\_x**: Independent variable expression to operate on. Can be a constant, column, or function, and any combination of operators. #### regr\_count Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} regr_count(expression_y, expression_x) ``` Counts the number of non-null paired data points. * **expression\_y**: Dependent variable expression to operate on. Can be a constant, column, or function, and any combination of operators. * **expression\_x**: Independent variable expression to operate on. Can be a constant, column, or function, and any combination of operators. #### regr\_intercept Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} regr_intercept(expression_y, expression_x) ``` Computes the y-intercept of the linear regression line. For the equation (y = kx + b), this function returns b. * **expression\_y**: Dependent variable expression to operate on. Can be a constant, column, or function, and any combination of operators. * **expression\_x**: Independent variable expression to operate on. Can be a constant, column, or function, and any combination of operators. #### regr\_r2 Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} regr_r2(expression_y, expression_x) ``` Computes the square of the correlation coefficient between the independent and dependent variables. * **expression\_y**: Dependent variable expression to operate on. Can be a constant, column, or function, and any combination of operators. * **expression\_x**: Independent variable expression to operate on. Can be a constant, column, or function, and any combination of operators. #### regr\_slope Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} regr_slope(expression_y, expression_x) ``` Returns the slope of the linear regression line for non-null pairs in aggregate columns. Given input column Y and X: regr\_slope(Y, X) returns the slope (k in Y = k\*X + b) using minimal RSS fitting. * **expression\_y**: Dependent variable expression to operate on. Can be a constant, column, or function, and any combination of operators. * **expression\_x**: Independent variable expression to operate on. Can be a constant, column, or function, and any combination of operators. #### regr\_sxx Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} regr_sxx(expression_y, expression_x) ``` Computes the sum of squares of the independent variable. * **expression\_y**: Dependent variable expression to operate on. Can be a constant, column, or function, and any combination of operators. * **expression\_x**: Independent variable expression to operate on. Can be a constant, column, or function, and any combination of operators. #### regr\_sxy Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} regr_sxy(expression_y, expression_x) ``` Computes the sum of products of paired data points. * **expression\_y**: Dependent variable expression to operate on. Can be a constant, column, or function, and any combination of operators. * **expression\_x**: Independent variable expression to operate on. Can be a constant, column, or function, and any combination of operators. #### regr\_syy Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} regr_syy(expression_y, expression_x) ``` Computes the sum of squares of the dependent variable. * **expression\_y**: Dependent variable expression to operate on. Can be a constant, column, or function, and any combination of operators. * **expression\_x**: Independent variable expression to operate on. Can be a constant, column, or function, and any combination of operators. #### stddev Kind: Aggregate. Aliases: `std`, `stddev_samp`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} stddev(expression) ``` Returns the standard deviation of a set of numbers. * **expression**: The expression to operate on. Can be a constant, column, or function, and any combination of operators. #### stddev\_pop Kind: Aggregate. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} stddev_pop(expression) ``` Returns the population standard deviation of a set of numbers. * **expression**: The expression to operate on. Can be a constant, column, or function, and any combination of operators. ### Ranking Functions #### cume\_dist Kind: Window. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} cume_dist() ``` Relative rank of the current row: (number of rows preceding or peer with the current row) / (total rows). #### dense\_rank Kind: Window. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} dense_rank() ``` Returns the rank of the current row without gaps. This function ranks rows in a dense manner, meaning consecutive ranks are assigned even for identical values. #### ntile Kind: Window. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} ntile(expression) ``` Integer ranging from 1 to the argument value, dividing the partition as equally as possible * **expression**: An integer describing the number groups the partition should be split into #### percent\_rank Kind: Window. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} percent_rank() ``` Returns the percentage rank of the current row within its partition. The value ranges from 0 to 1 and is computed as `(rank - 1) / (total_rows - 1)`. #### rank Kind: Window. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} rank() ``` Returns the rank of the current row within its partition, allowing gaps between ranks. This function provides a ranking similar to `row_number`, but skips ranks for identical values. #### row\_number Kind: Window. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} row_number() ``` Number of the current row within its partition, counting from 1. ### Time and Date Functions #### current\_date Kind: Scalar. Aliases: `today`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} current_date() (optional) SET datafusion.execution.time_zone = '+00:00'; SELECT current_date(); ``` Returns the current date in the session time zone. The `current_date()` return value is determined at query time and will return the same date, no matter when in the query plan the function executes. #### current\_time Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} current_time() (optional) SET datafusion.execution.time_zone = '+00:00'; SELECT current_time(); ``` Returns the current time in the session time zone. The `current_time()` return value is determined at query time and will return the same time, no matter when in the query plan the function executes. The session time zone can be set using the statement 'SET datafusion.execution.time\_zone = desired time zone'. The time zone can be a value like +00:00, 'Europe/London' etc. #### date\_bin Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} date_bin(interval, expression, origin-timestamp) ``` Calculates time intervals and returns the start of the interval nearest to the specified timestamp. Use `date_bin` to downsample time series data by grouping rows into time-based "bins" or "windows" and applying an aggregate or selector function to each window. For example, if you "bin" or "window" data into 15 minute intervals, an input timestamp of `2023-01-01T18:18:18Z` will be updated to the start time of the 15 minute bin it is in: `2023-01-01T18:15:00Z`. * **interval**: Bin interval. * **expression**: Time expression to operate on. Can be a constant, column, or function. * **origin-timestamp**: Optional. Starting point used to determine bin boundaries. If not specified defaults 1970-01-01T00:00:00Z (the UNIX epoch in UTC). The following intervals are supported: * nanoseconds * microseconds * milliseconds * seconds * minutes * hours * days * weeks * months * years * century #### date\_part Kind: Scalar. Aliases: `datepart`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} date_part(part, expression) ``` Returns the specified part of the date as an integer. * **part**: Part of the date to return. The following date parts are supported: * year * isoyear (ISO 8601 week-numbering year) * quarter (emits value in inclusive range \[1, 4] based on which quartile of the year the date is in) * month * week (week of the year) * day (day of the month) * hour * minute * second * millisecond * microsecond * nanosecond * dow (day of the week where Sunday is 0) * doy (day of the year) * epoch (seconds since Unix epoch for timestamps/dates, total seconds for intervals) * isodow (ISO 8601 day of the week where Monday is 1 and Sunday is 7) * **expression**: Time expression to operate on. Can be a constant, column, or function. #### date\_trunc Kind: Scalar. Aliases: `datetrunc`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} date_trunc(precision, expression) ``` Truncates a timestamp or time value to a specified precision. * **precision**: Time precision to truncate to. The following precisions are supported: For Timestamp types: * year / YEAR * quarter / QUARTER * month / MONTH * week / WEEK * day / DAY * hour / HOUR * minute / MINUTE * second / SECOND * millisecond / MILLISECOND * microsecond / MICROSECOND For Time types (hour, minute, second, millisecond, microsecond only): * hour / HOUR * minute / MINUTE * second / SECOND * millisecond / MILLISECOND * microsecond / MICROSECOND * **expression**: Timestamp or time expression to operate on. Can be a constant, column, or function. #### from\_unixtime Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} from_unixtime(expression[, timezone]) ``` Converts an integer to RFC3339 timestamp format (`YYYY-MM-DDT00:00:00.000000000Z`). Integers and unsigned integers are interpreted as seconds since the unix epoch (`1970-01-01T00:00:00Z`) return the corresponding timestamp. * **expression**: The expression to operate on. Can be a constant, column, or function, and any combination of operators. * **timezone**: Optional timezone to use when converting the integer to a timestamp. If not provided, the default timezone is UTC. #### make\_date Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} make_date(year, month, day) ``` Make a date from year/month/day component parts. * **year**: Year to use when making the date. Can be a constant, column or function, and any combination of arithmetic operators. * **month**: Month to use when making the date. Can be a constant, column or function, and any combination of arithmetic operators. * **day**: Day to use when making the date. Can be a constant, column or function, and any combination of arithmetic operators. #### make\_time Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} make_time(hour, minute, second) ``` Make a time from hour/minute/second component parts. * **hour**: Hour to use when making the time. Can be a constant, column or function, and any combination of arithmetic operators. * **minute**: Minute to use when making the time. Can be a constant, column or function, and any combination of arithmetic operators. * **second**: Second to use when making the time. Can be a constant, column or function, and any combination of arithmetic operators. #### now Kind: Scalar. Aliases: `current_timestamp`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} now() ``` Returns the current timestamp in the system configured timezone (None by default). The `now()` return value is determined at query time and will return the same timestamp, no matter when in the query plan the function executes. #### to\_char Kind: Scalar. Aliases: `date_format`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} to_char(expression, format) ``` Returns a string representation of a date, time, timestamp or duration based on a [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html). Unlike the PostgreSQL equivalent of this function numerical formatting is not supported. * **expression**: Expression to operate on. Can be a constant, column, or function that results in a date, time, timestamp or duration. * **format**: A [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html) string to use to convert the expression. * **day**: Day to use when making the date. Can be a constant, column or function, and any combination of arithmetic operators. #### to\_date Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} to_date('2017-05-31', '%Y-%m-%d') ``` Converts a value to a date (`YYYY-MM-DD`). Supports strings, numeric and timestamp types as input. Strings are parsed as YYYY-MM-DD (e.g. '2023-07-20') if no [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html)s are provided. Integers and doubles are interpreted as days since the unix epoch (`1970-01-01T00:00:00Z`). Returns the corresponding date. Note: `to_date` returns Date32, which represents its values as the number of days since unix epoch(`1970-01-01`) stored as signed 32 bit value. The largest supported date value is `9999-12-31`. * **expression**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **format\_n**: Optional [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html) strings to use to parse the expression. Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully parse the expression an error will be returned. #### to\_local\_time Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} to_local_time(expression) ``` Converts a timestamp with a timezone to a timestamp without a timezone (with no offset or timezone information). This function handles daylight saving time changes. * **expression**: Time expression to operate on. Can be a constant, column, or function. #### to\_time Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} to_time('12:30:45', '%H:%M:%S') ``` Converts a value to a time (`HH:MM:SS.nnnnnnnnn`). Supports strings and timestamps as input. Strings are parsed as `HH:MM:SS`, `HH:MM:SS.nnnnnnnnn`, or `HH:MM` if no [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html)s are provided. Timestamps will have the time portion extracted. Returns the corresponding time. Note: `to_time` returns Time64(Nanosecond), which represents the time of day in nanoseconds since midnight. * **expression**: String or Timestamp expression to operate on. Can be a constant, column, or function, and any combination of operators. * **format\_n**: Optional [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html) strings to use to parse the expression. Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully parse the expression an error will be returned. #### to\_timestamp Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} to_timestamp(expression[, ..., format_n]) ``` Converts a value to a timestamp (`YYYY-MM-DDT00:00:00.000000`) in the session time zone. Supports strings, integer, unsigned integer, and double types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00') if no [Chrono formats](https://docs.rs/chrono/latest/chrono/format/strftime/index.html) are provided. Strings that parse without a time zone are treated as if they are in the session time zone, or UTC if no session time zone is set. Integers, unsigned integers, and doubles are interpreted as seconds since the unix epoch (`1970-01-01T00:00:00Z`). Note: `to_timestamp` returns `Timestamp(ns, TimeZone)` where the time zone is the session time zone. The supported range for integer input is between`-9223372037` and `9223372036`. Supported range for string input is between `1677-09-21T00:12:44.0` and `2262-04-11T23:47:16.0`. Please use `to_timestamp_seconds` for the input outside of supported bounds. The session time zone can be set using the statement `SET TIMEZONE = 'desired time zone'`. The time zone can be a value like +00:00, 'Europe/London' etc. * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. * **format\_n**: Optional [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html) strings to use to parse the expression. Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully parse the expression an error will be returned. Note: parsing of named timezones (e.g. 'America/New\_York') using %Z is only supported at the end of the string preceded by a space. #### to\_timestamp\_micros Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} to_timestamp_micros(expression[, ..., format_n]) ``` Converts a value to a timestamp (`YYYY-MM-DDT00:00:00.000000`) in the session time zone. Supports strings, integer, unsigned integer, and double types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00') if no [Chrono formats](https://docs.rs/chrono/latest/chrono/format/strftime/index.html) are provided. Strings that parse without a time zone are treated as if they are in the session time zone, or UTC if no session time zone is set. Integers, unsigned integers, and doubles are interpreted as microseconds since the unix epoch (`1970-01-01T00:00:00Z`). The session time zone can be set using the statement `SET TIMEZONE = 'desired time zone'`. The time zone can be a value like +00:00, 'Europe/London' etc. * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. * **format\_n**: Optional [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html) strings to use to parse the expression. Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully parse the expression an error will be returned. Note: parsing of named timezones (e.g. 'America/New\_York') using %Z is only supported at the end of the string preceded by a space. #### to\_timestamp\_millis Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} to_timestamp_millis(expression[, ..., format_n]) ``` Converts a value to a timestamp (`YYYY-MM-DDT00:00:00.000`) in the session time zone. Supports strings, integer, unsigned integer, and double types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00') if no [Chrono formats](https://docs.rs/chrono/latest/chrono/format/strftime/index.html) are provided. Strings that parse without a time zone are treated as if they are in the session time zone, or UTC if no session time zone is set. Integers, unsigned integers, and doubles are interpreted as milliseconds since the unix epoch (`1970-01-01T00:00:00Z`). The session time zone can be set using the statement `SET TIMEZONE = 'desired time zone'`. The time zone can be a value like +00:00, 'Europe/London' etc. * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. * **format\_n**: Optional [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html) strings to use to parse the expression. Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully parse the expression an error will be returned. Note: parsing of named timezones (e.g. 'America/New\_York') using %Z is only supported at the end of the string preceded by a space. #### to\_timestamp\_nanos Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} to_timestamp_nanos(expression[, ..., format_n]) ``` Converts a value to a timestamp (`YYYY-MM-DDT00:00:00.000000000`) in the session time zone. Supports strings, integer, unsigned integer, and double types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00') if no [Chrono formats](https://docs.rs/chrono/latest/chrono/format/strftime/index.html) are provided. Strings that parse without a time zone are treated as if they are in the session time zone. Integers, unsigned integers, and doubles are interpreted as nanoseconds since the unix epoch (`1970-01-01T00:00:00Z`). The session time zone can be set using the statement `SET TIMEZONE = 'desired time zone'`. The time zone can be a value like +00:00, 'Europe/London' etc. * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. * **format\_n**: Optional [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html) strings to use to parse the expression. Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully parse the expression an error will be returned. Note: parsing of named timezones (e.g. 'America/New\_York') using %Z is only supported at the end of the string preceded by a space. #### to\_timestamp\_seconds Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} to_timestamp_seconds(expression[, ..., format_n]) ``` Converts a value to a timestamp (`YYYY-MM-DDT00:00:00`) in the session time zone. Supports strings, integer, unsigned integer, and double types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00') if no [Chrono formats](https://docs.rs/chrono/latest/chrono/format/strftime/index.html) are provided. Strings that parse without a time zone are treated as if they are in the session time zone, or UTC if no session time zone is set. Integers, unsigned integers, and doubles are interpreted as seconds since the unix epoch (`1970-01-01T00:00:00Z`). The session time zone can be set using the statement `SET TIMEZONE = 'desired time zone'`. The time zone can be a value like +00:00, 'Europe/London' etc. * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. * **format\_n**: Optional [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html) strings to use to parse the expression. Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully parse the expression an error will be returned. Note: parsing of named timezones (e.g. 'America/New\_York') using %Z is only supported at the end of the string preceded by a space. #### to\_unixtime Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} to_unixtime(expression[, ..., format_n]) ``` Converts a value to seconds since the unix epoch (`1970-01-01T00:00:00`). Supports strings, dates, timestamps, integer, unsigned integer, and float types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00') if no [Chrono formats](https://docs.rs/chrono/latest/chrono/format/strftime/index.html) are provided. Integers, unsigned integers, and floats are interpreted as seconds since the unix epoch (`1970-01-01T00:00:00`). * **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. * **format\_n**: Optional [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html) strings to use to parse the expression. Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully parse the expression an error will be returned. ### Binary String Functions #### decode Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} decode(expression, format) ``` Decode binary data from textual representation in string. * **expression**: Expression containing encoded string data * **format**: Same arguments as [encode](#encode) #### encode Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} encode(expression, format) ``` Encode binary data into a textual representation. * **expression**: Expression containing string or binary data * **format**: Supported formats are: `base64`, `base64pad`, `hex` ### Hashing Functions #### digest Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} digest(expression, algorithm) ``` Computes the binary hash of an expression using the specified algorithm. * **expression**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **algorithm**: String expression specifying algorithm to use. Must be one of: * md5 * sha224 * sha256 * sha384 * sha512 * blake2s * blake2b * blake3 #### md5 Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} md5(expression) ``` Computes an MD5 128-bit checksum for a string expression. * **expression**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. #### sha224 Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} sha224(expression) ``` Computes the SHA-224 hash of a binary string. * **expression**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. #### sha256 Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} sha256(expression) ``` Computes the SHA-256 hash of a binary string. * **expression**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. #### sha384 Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} sha384(expression) ``` Computes the SHA-384 hash of a binary string. * **expression**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. #### sha512 Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} sha512(expression) ``` Computes the SHA-512 hash of a binary string. * **expression**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. ### Analytical Functions #### first\_value Kind: Window. Aliases: `first`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} first_value(expression) ``` Returns value evaluated at the row that is the first row of the window frame. * **expression**: Expression to operate on #### lag Kind: Window. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} lag(expression, offset, default) ``` Returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default (which must be of the same type as value). * **expression**: Expression to operate on * **offset**: Integer. Specifies how many rows back the value of expression should be retrieved. Defaults to 1. * **default**: The default value if the offset is not within the partition. Must be of the same type as expression. #### last\_value Kind: Window. Aliases: `last`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} last_value(expression) ``` Returns value evaluated at the row that is the last row of the window frame. * **expression**: Expression to operate on #### lead Kind: Window. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} lead(expression, offset, default) ``` Returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default (which must be of the same type as value). * **expression**: Expression to operate on * **offset**: Integer. Specifies how many rows forward the value of expression should be retrieved. Defaults to 1. * **default**: The default value if the offset is not within the partition. Must be of the same type as expression. #### nth\_value Kind: Window. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} nth_value(expression, n) ``` Returns the value evaluated at the nth row of the window frame (counting from 1). Returns NULL if no such row exists. * **expression**: The column from which to retrieve the nth value. * **n**: Integer. Specifies the row number (starting from 1) in the window frame. ### Scalar #### json\_extract\_string Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} json_extract_string ``` No additional registry description. ### Map Functions #### map Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} map(key, value) map(key: value) make_map(['key1', 'key2'], ['value1', 'value2']) ``` Returns an Arrow map with the specified key-value pairs. The `make_map` function creates a map from two lists: one for keys and one for values. Each key must be unique and non-null. * **key**: For `map`: Expression to be used for key. Can be a constant, column, function, or any combination of arithmetic or string operators. For `make_map`: The list of keys to be used in the map. Each key must be unique and non-null. * **value**: For `map`: Expression to be used for value. Can be a constant, column, function, or any combination of arithmetic or string operators. For `make_map`: The list of values to be mapped to the corresponding keys. #### map\_entries Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} map_entries(map) ``` Returns a list of all entries in the map. * **map**: Map expression. Can be a constant, column, or function, and any combination of map operators. #### map\_extract Kind: Scalar. Aliases: `element_at`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} map_extract(map, key) ``` Returns a list containing the value for the given key or an empty list if the key is not present in the map. * **map**: Map expression. Can be a constant, column, or function, and any combination of map operators. * **key**: Key to extract from the map. Can be a constant, column, or function, any combination of arithmetic or string operators, or a named expression of the previously listed. #### map\_keys Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} map_keys(map) ``` Returns a list of all keys in the map. * **map**: Map expression. Can be a constant, column, or function, and any combination of map operators. #### map\_values Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} map_values(map) ``` Returns a list of all values in the map. * **map**: Map expression. Can be a constant, column, or function, and any combination of map operators. ### Struct Functions #### named\_struct Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} named_struct(expression1_name, expression1_input[, ..., expression_n_name, expression_n_input]) ``` Returns an Arrow struct using the specified name and input expressions pairs. For information on comparing and ordering struct values (including `NULL` handling), see [Comparison and Ordering](https://datafusion.apache.org/user-guide/sql/struct_coercion.html#comparison-and-ordering). * **expression\_n\_name**: Name of the column field. Must be a constant string. * **expression\_n\_input**: Expression to include in the output struct. Can be a constant, column, or function, and any combination of arithmetic or string operators. #### struct Kind: Scalar. Aliases: `row`. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} struct(expression1[, ..., expression_n]) ``` Returns an Arrow struct using the specified input expressions optionally named. Fields in the returned struct use the optional name or the `cN` naming convention. For example: `c0`, `c1`, `c2`, etc. For information on comparing and ordering struct values (including `NULL` handling), see [Comparison and Ordering](https://datafusion.apache.org/user-guide/sql/struct_coercion.html#comparison-and-ordering). * **expression1, expression\_n**: Expression to include in the output struct. Can be a constant, column, or function, any combination of arithmetic or string operators. ### Regular Expression Functions #### regexp\_count Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} regexp_count(str, regexp[, start, flags]) ``` Returns the number of matches that a [regular expression](https://docs.rs/regex/latest/regex/#syntax) has in a string. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **regexp**: Regular expression to operate on. Can be a constant, column, or function, and any combination of operators. * **start**: - **start**: Optional start position (the first position is 1) to search for the regular expression. Can be a constant, column, or function. * **flags**: Optional regular expression flags that control the behavior of the regular expression. The following flags are supported: * **i**: case-insensitive: letters match both upper and lower case * **m**: multi-line mode: ^ and \$ match begin/end of line * **s**: allow . to match \n * **R**: enables CRLF mode: when multi-line mode is enabled, \r\n is used * **U**: swap the meaning of x\* and x\*? #### regexp\_instr Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} regexp_instr(str, regexp[, start[, N[, flags[, subexpr]]]]) ``` Returns the position in a string where the specified occurrence of a POSIX regular expression is located. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **regexp**: Regular expression to operate on. Can be a constant, column, or function, and any combination of operators. * **start**: - **start**: Optional start position (the first position is 1) to search for the regular expression. Can be a constant, column, or function. Defaults to 1 * **N**: - **N**: Optional The N-th occurrence of pattern to find. Defaults to 1 (first match). Can be a constant, column, or function. * **flags**: Optional regular expression flags that control the behavior of the regular expression. The following flags are supported: * **i**: case-insensitive: letters match both upper and lower case * **m**: multi-line mode: ^ and \$ match begin/end of line * **s**: allow . to match \n * **R**: enables CRLF mode: when multi-line mode is enabled, \r\n is used * **U**: swap the meaning of x\* and x\*? * **subexpr**: Optional Specifies which capture group (subexpression) to return the position for. Defaults to 0, which returns the position of the entire match. #### regexp\_like Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} regexp_like(str, regexp[, flags]) ``` Returns true if a [regular expression](https://docs.rs/regex/latest/regex/#syntax) has at least one match in a string, false otherwise. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **regexp**: Regular expression to operate on. Can be a constant, column, or function, and any combination of operators. * **flags**: Optional regular expression flags that control the behavior of the regular expression. The following flags are supported: * **i**: case-insensitive: letters match both upper and lower case * **m**: multi-line mode: ^ and \$ match begin/end of line * **s**: allow . to match \n * **R**: enables CRLF mode: when multi-line mode is enabled, \r\n is used * **U**: swap the meaning of x\* and x\*? #### regexp\_match Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} regexp_match(str, regexp[, flags]) ``` Returns the first [regular expression](https://docs.rs/regex/latest/regex/#syntax) matches in a string. * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **regexp**: Regular expression to match against. Can be a constant, column, or function. * **flags**: Optional regular expression flags that control the behavior of the regular expression. The following flags are supported: * **i**: case-insensitive: letters match both upper and lower case * **m**: multi-line mode: ^ and \$ match begin/end of line * **s**: allow . to match \n * **R**: enables CRLF mode: when multi-line mode is enabled, \r\n is used * **U**: swap the meaning of x\* and x\*? #### regexp\_replace Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} regexp_replace(str, regexp, replacement[, flags]) ``` Replaces substrings in a string that match a [regular expression](https://docs.rs/regex/latest/regex/#syntax). * **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators. * **regexp**: Regular expression to match against. Can be a constant, column, or function. * **replacement**: Replacement string expression to operate on. Can be a constant, column, or function, and any combination of operators. * **flags**: Optional regular expression flags that control the behavior of the regular expression. The following flags are supported: * **g**: (global) Search globally and don't return after the first match * **i**: case-insensitive: letters match both upper and lower case * **m**: multi-line mode: ^ and \$ match begin/end of line * **s**: allow . to match \n * **R**: enables CRLF mode: when multi-line mode is enabled, \r\n is used * **U**: swap the meaning of x\* and x\*? ### Union Functions #### union\_extract Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} union_extract(union, field_name) ``` Returns the value of the given field in the union when selected, or NULL otherwise. * **union**: Union expression to operate on. Can be a constant, column, or function, and any combination of operators. * **field\_name**: String expression to operate on. Must be a constant. #### union\_tag Kind: Scalar. ```text theme={"theme":{"light":"github-light","dark":"github-dark"}} union_tag(union_expression) ``` Returns the name of the currently selected field in the union * **union**: Union expression to operate on. Can be a constant, column, or function, and any combination of operators. Signatures and argument descriptions come from the Unified registry and DataFusion 54.1.0 (Apache-2.0). Registration describes the language registry; backend and connector restrictions still apply. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.