# Scalar Functions

> **Note**
>
> Spice is built on [Apache DataFusion](https://datafusion.apache.org/) and uses the PostgreSQL dialect, even when querying datasources with different SQL dialects. When using a data accelerator like DuckDB, function support is specific to each acceleration engine, and not all functions are supported by all acceleration engines.

Scalar functions help transform, compute, and manipulate data at the row level. These functions are evaluated for each row in a query result and return a single value per invocation. Spice.ai supports a broad set of scalar functions, including math, string, conditional, date/time, array, struct, map, regular expression, and hashing functions. The function set closely follows the PostgreSQL dialect.

## Function Categories

- [Math Functions](https://spice.ai/docs/sql/scalar-functions#math-functions)
- [Conditional Functions](https://spice.ai/docs/sql/scalar-functions#conditional-functions)
- [String Functions](https://spice.ai/docs/sql/scalar-functions#string-functions)
- [Binary String Functions](https://spice.ai/docs/sql/scalar-functions#binary-string-functions)
- [Regular Expression Functions](https://spice.ai/docs/sql/scalar-functions#regular-expression-functions)
- [Time and Date Functions](https://spice.ai/docs/sql/scalar-functions#time-and-date-functions)
- [Array Functions](https://spice.ai/docs/sql/scalar-functions#array-functions)
- [Struct Functions](https://spice.ai/docs/sql/scalar-functions#struct-functions)
- [Map Functions](https://spice.ai/docs/sql/scalar-functions#map-functions)
- [Hashing Functions](https://spice.ai/docs/sql/scalar-functions#hashing-functions)
- [Encoding Functions](https://spice.ai/docs/sql/scalar-functions#encoding-functions)
- [Union Functions](https://spice.ai/docs/sql/scalar-functions#union-functions)
- [Metadata Functions](https://spice.ai/docs/sql/scalar-functions#metadata-functions)
- [Other Functions](https://spice.ai/docs/sql/scalar-functions#other-functions)

Spark-compatible scalar functions registered by Spice are documented here only when their Spark-specific behavior differs from the PostgreSQL equivalent, or when they have no PostgreSQL analogue. For functions not listed below, refer to the [Spark SQL built-in function reference](https://spark.apache.org/docs/latest/api/sql/index.html) for semantics.

---

## Math Functions

Math functions in Spice.ai SQL help perform numeric calculations, transformations, and analysis. These functions operate on numeric expressions, which can be constants, columns, or results of other functions and operators. The following math functions are supported:

- [abs](https://spice.ai/docs/sql/scalar-functions#abs)
- [acos](https://spice.ai/docs/sql/scalar-functions#acos)
- [acosh](https://spice.ai/docs/sql/scalar-functions#acosh)
- [asin](https://spice.ai/docs/sql/scalar-functions#asin)
- [asinh](https://spice.ai/docs/sql/scalar-functions#asinh)
- [atan](https://spice.ai/docs/sql/scalar-functions#atan)
- [atan2](https://spice.ai/docs/sql/scalar-functions#atan2)
- [atanh](https://spice.ai/docs/sql/scalar-functions#atanh)
- [cbrt](https://spice.ai/docs/sql/scalar-functions#cbrt)
- [ceil](https://spice.ai/docs/sql/scalar-functions#ceil)
- [cos](https://spice.ai/docs/sql/scalar-functions#cos)
- [cosh](https://spice.ai/docs/sql/scalar-functions#cosh)
- [cot](https://spice.ai/docs/sql/scalar-functions#cot)
- [degrees](https://spice.ai/docs/sql/scalar-functions#degrees)
- [exp](https://spice.ai/docs/sql/scalar-functions#exp)
- [factorial](https://spice.ai/docs/sql/scalar-functions#factorial)
- [floor](https://spice.ai/docs/sql/scalar-functions#floor)
- [gcd](https://spice.ai/docs/sql/scalar-functions#gcd)
- [isnan](https://spice.ai/docs/sql/scalar-functions#isnan)
- [iszero](https://spice.ai/docs/sql/scalar-functions#iszero)
- [lcm](https://spice.ai/docs/sql/scalar-functions#lcm)
- [mod](https://spice.ai/docs/sql/scalar-functions#mod)
- [pmod](https://spice.ai/docs/sql/scalar-functions#pmod)
- [ln](https://spice.ai/docs/sql/scalar-functions#ln)
- [log](https://spice.ai/docs/sql/scalar-functions#log)
- [log10](https://spice.ai/docs/sql/scalar-functions#log10)
- [log2](https://spice.ai/docs/sql/scalar-functions#log2)
- [nanvl](https://spice.ai/docs/sql/scalar-functions#nanvl)
- [pi](https://spice.ai/docs/sql/scalar-functions#pi)
- [pow](https://spice.ai/docs/sql/scalar-functions#pow-and-power)
- [power](https://spice.ai/docs/sql/scalar-functions#pow-and-power)
- [radians](https://spice.ai/docs/sql/scalar-functions#radians)
- [random](https://spice.ai/docs/sql/scalar-functions#random)
- [round](https://spice.ai/docs/sql/scalar-functions#round)
- [rint](https://spice.ai/docs/sql/scalar-functions#rint)
- [signum](https://spice.ai/docs/sql/scalar-functions#signum)
- [sin](https://spice.ai/docs/sql/scalar-functions#sin)
- [sinh](https://spice.ai/docs/sql/scalar-functions#sinh)
- [sqrt](https://spice.ai/docs/sql/scalar-functions#sqrt)
- [tan](https://spice.ai/docs/sql/scalar-functions#tan)
- [tanh](https://spice.ai/docs/sql/scalar-functions#tanh)
- [trunc](https://spice.ai/docs/sql/scalar-functions#trunc)
- [width\_bucket](https://spice.ai/docs/sql/scalar-functions#width_bucket)

### `abs`

Returns the absolute value of a numeric expression. If the input is negative, the result is its positive equivalent; if the input is positive or zero, the result is unchanged.

```sql
abs(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Numeric value to evaluate. Accepts constants, columns, or expressions.

#### Example

```sql
> select abs(-5);
+-------------+
| abs(Int64(-5)) |
+-------------+
| 5           |
+-------------+
```

### `acos`

Returns the arc cosine (inverse cosine) of a numeric expression. The input must be in the range \[-1, 1]. The result is in radians.

```sql
acos(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Value between -1 and 1.

### `acosh`

Returns the inverse hyperbolic cosine of a numeric expression. The input must be greater than or equal to 1.

```sql
acosh(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Value greater than or equal to 1.

### `asin`

Returns the arc sine (inverse sine) of a numeric expression. The input must be in the range \[-1, 1]. The result is in radians.

```sql
asin(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Value between -1 and 1.

### `asinh`

Returns the inverse hyperbolic sine of a numeric expression.

```sql
asinh(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Numeric value.

### `atan`

Returns the arc tangent (inverse tangent) of a numeric expression. The result is in radians.

```sql
atan(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Numeric value.

### `atan2`

Returns the arc tangent of the quotient of its arguments, that is, atan(expression\_y / expression\_x). The result is in radians and takes into account the signs of both arguments to determine the correct quadrant.

```sql
atan2(expression_y, expression_x)
```

#### Arguments

- **expression\_y**: Numerator value.
- **expression\_x**: Denominator value.

### `atanh`

Returns the inverse hyperbolic tangent of a numeric expression. The input must be in the range (-1, 1).

```sql
atanh(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Value between -1 and 1 (exclusive).

### `cbrt`

Returns the cube root of a numeric expression.

```sql
cbrt(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Numeric value.

### `ceil`

Returns the smallest integer greater than or equal to the input value.

```sql
ceil(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Numeric value.

### `cos`

Returns the cosine of a numeric expression, where the input is in radians.

```sql
cos(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Value in radians.

### `cosh`

Returns the hyperbolic cosine of a numeric expression.

```sql
cosh(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Numeric value.

### `cot`

Returns the cotangent of a numeric expression, where the input is in radians.

```sql
cot(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Value in radians.

### `degrees`

Converts radians to degrees.

```sql
degrees(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Value in radians.

### `exp`

Returns the value of e (Euler's number) raised to the power of the input value.

```sql
exp(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Exponent value.

### `factorial`

Returns the factorial of a non-negative integer. For values less than 2, returns 1.

```sql
factorial(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Non-negative integer value.

### `floor`

Returns the largest integer less than or equal to the input value.

```sql
floor(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Numeric value.

### `gcd`

Returns the greatest common divisor of two integer expressions. If both inputs are zero, returns 0.

```sql
gcd(expression_x, expression_y)
```

#### Arguments

- **expression\_x**: First integer value.
- **expression\_y**: Second integer value.

### `isnan`

Returns true if the input is NaN (not a number), otherwise returns false.

```sql
isnan(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Numeric value.

### `iszero`

Returns true if the input is +0.0 or -0.0, otherwise returns false.

```sql
iszero(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Numeric value.

### `lcm`

Returns the least common multiple of two integer expressions. If either input is zero, returns 0.

```sql
lcm(expression_x, expression_y)
```

#### Arguments

- **expression\_x**: First integer value.
- **expression\_y**: Second integer value.

### `mod`

Returns the remainder after dividing the first argument by the second, matching the Spark SQL `%`/`mod` semantics for signed values.

```sql
mod(dividend, divisor)
```

#### Arguments

- **dividend**: Numeric expression to divide.
- **divisor**: Numeric expression that cannot be zero.

#### Example

```sql
> select mod(-10, 3);
+----------------------------+
| mod(Int64(-10),Int64(3))   |
+----------------------------+
| -1                         |
+----------------------------+
```

Reference: [Spark SQL `mod`](https://spark.apache.org/docs/latest/api/sql/index.html#mod).

### `pmod`

Returns a positive remainder for integer or floating-point division. When the standard remainder is negative, the divisor is added to produce a non-negative result, mirroring Spark SQL behavior.

```sql
pmod(dividend, divisor)
```

#### Arguments

- **dividend**: Numeric expression to divide.
- **divisor**: Numeric expression that cannot be zero.

#### Example

```sql
> select pmod(-10, 3);
+-----------------------------+
| pmod(Int64(-10),Int64(3))   |
+-----------------------------+
| 2                           |
+-----------------------------+
```

Reference: [Spark SQL `pmod`](https://spark.apache.org/docs/latest/api/sql/index.html#pmod).

### `ln`

Returns the natural logarithm (base e) of a numeric expression.

```sql
ln(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Positive numeric value.

### `log`

Returns the logarithm of a numeric expression. If a base is provided, returns the logarithm to that base; otherwise, returns the base-10 logarithm.

```sql
log(base, numeric_expression)
log(numeric_expression)
```

#### Arguments

- **base**: Base of the logarithm (optional).
- **numeric\_expression**: Positive numeric value.

### `log10`

Returns the base-10 logarithm of a numeric expression.

```sql
log10(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Positive numeric value.

### `log2`

Returns the base-2 logarithm of a numeric expression.

```sql
log2(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Positive numeric value.

### `nanvl`

Returns the first argument if it is not NaN; otherwise, returns the second argument.

```sql
nanvl(expression_x, expression_y)
```

#### Arguments

- **expression\_x**: Value to return if not NaN.
- **expression\_y**: Value to return if the first argument is NaN.

### `pi`

Returns an approximate value of π (pi).

```sql
pi()
```

### `pow` and `power`

Returns the value of the first argument raised to the power of the second argument. `pow` is an alias for `power`.

```sql
power(base, exponent)
pow(base, exponent)
```

#### Arguments

- **base**: Numeric value to raise.
- **exponent**: Power to raise the base to.

### `radians`

Converts degrees to radians.

```sql
radians(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Value in degrees.

### `random`

Returns a random floating-point value in the range \[0, 1). The random seed is unique for each row.

```sql
random()
```

### `round`

Rounds a numeric expression to the nearest integer or to a specified number of decimal places.

```sql
round(numeric_expression[, decimal_places])
```

#### Arguments

- **numeric\_expression**: Value to round.
- **decimal\_places**: Optional. Number of decimal places to round to. Defaults to 0.

### `rint`

Rounds a double-precision value to the nearest integer using IEEE-754 "round to nearest, ties to even" rules and returns the rounded value as a floating-point number, matching Spark SQL semantics.

```sql
rint(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Floating-point expression to round. Integers are implicitly cast to double.

#### Example

```sql
> select rint(12.5);
+-------------------------+
| rint(Float64(12.5))     |
+-------------------------+
| 12.0                    |
+-------------------------+
```

Reference: [Spark SQL `rint`](https://spark.apache.org/docs/latest/api/sql/index.html#rint).

### `signum`

Returns the sign of a numeric expression. Returns -1 for negative numbers, 1 for zero and positive numbers.

```sql
signum(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Numeric value.

### `sin`

Returns the sine of a numeric expression, where the input is in radians.

```sql
sin(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Value in radians.

### `sinh`

Returns the hyperbolic sine of a numeric expression.

```sql
sinh(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Numeric value.

### `sqrt`

Returns the square root of a numeric expression.

```sql
sqrt(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Non-negative numeric value.

### `tan`

Returns the tangent of a numeric expression, where the input is in radians.

```sql
tan(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Value in radians.

### `tanh`

Returns the hyperbolic tangent of a numeric expression.

```sql
tanh(numeric_expression)
```

#### Arguments

- **numeric\_expression**: Numeric value.

### `trunc`

Truncates a numeric expression to a whole number or to a specified number of decimal places. If `decimal_places` is positive, truncates digits to the right of the decimal point; if negative, truncates digits to the left.

```sql
trunc(numeric_expression[, decimal_places])
```

#### Arguments

- **numeric\_expression**: Value to truncate.
- **decimal\_places**: Optional. Number of decimal places to truncate to. Defaults to 0.

### `width_bucket`

Assigns a value to an equiwidth histogram bucket. Returns 0 when the value is below `min_value`, `num_bucket + 1` when it is above `max_value`, and otherwise the 1-based bucket index, mirroring Spark SQL behavior.

```sql
width_bucket(value, min_value, max_value, num_bucket)
```

#### Arguments

- **value**: Numeric or interval expression to bin.
- **min\_value**: Lower bound of the histogram range.
- **max\_value**: Upper bound of the histogram range.
- **num\_bucket**: Positive integer specifying the number of buckets.

#### Example

```sql
> select width_bucket(5.3, 0.2, 10.6, 5);
+--------------------------------------------------+
| width_bucket(Float64(5.3),Float64(0.2),Float64(10.6),Int64(5)) |
+--------------------------------------------------+
| 3                                                |
+--------------------------------------------------+
```

Reference: [Spark SQL `width_bucket`](https://spark.apache.org/docs/latest/api/sql/index.html#width_bucket).

---

## Conditional Functions

Conditional functions help handle null values, select among alternatives, and compare multiple expressions. These are useful for data cleaning and conditional logic in queries.

- [CASE](https://spice.ai/docs/sql/scalar-functions#case)
- [coalesce](https://spice.ai/docs/sql/scalar-functions#coalesce)
- [greatest](https://spice.ai/docs/sql/scalar-functions#greatest)
- [if](https://spice.ai/docs/sql/scalar-functions#if)
- [least](https://spice.ai/docs/sql/scalar-functions#least)
- [nullif](https://spice.ai/docs/sql/scalar-functions#nullif)
- [nvl](https://spice.ai/docs/sql/scalar-functions#nvl)
- [nvl2](https://spice.ai/docs/sql/scalar-functions#nvl2)

### `CASE`

Standard SQL `CASE` expression, supported in both simple and searched forms.

```sql
CASE expression
  WHEN value1 THEN result1
  [WHEN value2 THEN result2 ...]
  [ELSE default_result]
END

CASE
  WHEN condition1 THEN result1
  [WHEN condition2 THEN result2 ...]
  [ELSE default_result]
END
```

#### Example

```sql
> SELECT CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'C' END AS grade
  FROM (VALUES (95), (82), (70)) AS t(score);
+-------+
| grade |
+-------+
| A     |
| B     |
| C     |
+-------+
```

### `coalesce`

Returns the first non-null value from its arguments. Returns `NULL` only if every argument is `NULL`.

```sql
coalesce(expression1[, ..., expression_n])
```

#### Arguments

- **expression1, ..., expression\_n**: Expressions to evaluate in order. All arguments must share a common type.

#### Example

```sql
> SELECT coalesce(NULL, NULL, 'spice', 'datafusion');
+----------------------------------------------------+
| coalesce(NULL,NULL,Utf8("spice"),Utf8("datafusion")) |
+----------------------------------------------------+
| spice                                              |
+----------------------------------------------------+
```

### `greatest`

Returns the largest value among the arguments, ignoring `NULL`s. Returns `NULL` only if every argument is `NULL`.

```sql
greatest(expression1[, ..., expression_n])
```

#### Arguments

- **expression1, ..., expression\_n**: Expressions to compare. Must share a common type.

#### Example

```sql
> SELECT greatest(3, 7, NULL, 4);
+------------------------------------------------+
| greatest(Int64(3),Int64(7),NULL,Int64(4))      |
+------------------------------------------------+
| 7                                              |
+------------------------------------------------+
```

### `if`

Evaluates a boolean condition and returns one of two expressions, matching the Spark SQL `if` function semantics.

```sql
if(condition, true_value, false_value)
```

#### Arguments

- **condition**: Boolean expression determining which branch to take.
- **true\_value**: Expression to return when the condition evaluates to true.
- **false\_value**: Expression to return when the condition evaluates to false or NULL.

#### Example

```sql
> select if(temperature > 70, 'warm', 'cool') as label from (values (65), (72)) as t(temperature);
+------------+
| label      |
+------------+
| cool       |
| warm       |
+------------+
```

Reference: [Spark SQL `if`](https://spark.apache.org/docs/latest/api/sql/index.html#if).

### `least`

Returns the smallest value among the arguments, ignoring `NULL`s. Returns `NULL` only if every argument is `NULL`.

```sql
least(expression1[, ..., expression_n])
```

#### Arguments

- **expression1, ..., expression\_n**: Expressions to compare. Must share a common type.

#### Example

```sql
> SELECT least(3, 7, NULL, 4);
+--------------------------------------------+
| least(Int64(3),Int64(7),NULL,Int64(4))     |
+--------------------------------------------+
| 3                                          |
+--------------------------------------------+
```

### `nullif`

Returns `NULL` if `expression1` equals `expression2`; otherwise returns `expression1`. Useful for converting sentinel values to `NULL`.

```sql
nullif(expression1, expression2)
```

#### Arguments

- **expression1**: Value to return if it differs from `expression2`.
- **expression2**: Value to compare against.

#### Example

```sql
> SELECT nullif('unknown', 'unknown');
+-------------------------------------------+
| nullif(Utf8("unknown"),Utf8("unknown"))   |
+-------------------------------------------+
|                                           |
+-------------------------------------------+
> SELECT nullif('spice', 'unknown');
+-------------------------------------------+
| nullif(Utf8("spice"),Utf8("unknown"))     |
+-------------------------------------------+
| spice                                     |
+-------------------------------------------+
```

### `nvl`

Returns `expression2` when `expression1` is `NULL`; otherwise returns `expression1`. Equivalent to `coalesce(expression1, expression2)`.

```sql
nvl(expression1, expression2)
```

Alias: `ifnull`.

### `nvl2`

Returns `expression2` if `expression1` is not `NULL`; otherwise returns `expression3`.

```sql
nvl2(expression1, expression2, expression3)
```

## String Functions

String functions in Spice.ai SQL help manipulate, analyze, and transform text data. These functions operate on string expressions, which can be constants, columns, or results of other functions. The implementation closely follows the PostgreSQL dialect. The following string functions are supported:

- [ascii](https://spice.ai/docs/sql/scalar-functions#ascii)
- [bit\_length](https://spice.ai/docs/sql/scalar-functions#bit_length)
- [btrim](https://spice.ai/docs/sql/scalar-functions#btrim)
- [char\_length](https://spice.ai/docs/sql/scalar-functions#char_length)
- [character\_length](https://spice.ai/docs/sql/scalar-functions#character_length)
- [chr](https://spice.ai/docs/sql/scalar-functions#chr)
- [concat](https://spice.ai/docs/sql/scalar-functions#concat)
- [concat\_ws](https://spice.ai/docs/sql/scalar-functions#concat_ws)
- [contains](https://spice.ai/docs/sql/scalar-functions#contains)
- [like](https://spice.ai/docs/sql/scalar-functions#like)
- [ilike](https://spice.ai/docs/sql/scalar-functions#ilike)
- [ends\_with](https://spice.ai/docs/sql/scalar-functions#ends_with)
- [find\_in\_set](https://spice.ai/docs/sql/scalar-functions#find_in_set)
- [initcap](https://spice.ai/docs/sql/scalar-functions#initcap)
- [instr](https://spice.ai/docs/sql/scalar-functions#instr)
- [left](https://spice.ai/docs/sql/scalar-functions#left)
- [length](https://spice.ai/docs/sql/scalar-functions#length)
- [levenshtein](https://spice.ai/docs/sql/scalar-functions#levenshtein)
- [lower](https://spice.ai/docs/sql/scalar-functions#lower)
- [luhn\_check](https://spice.ai/docs/sql/scalar-functions#luhn_check)
- [lpad](https://spice.ai/docs/sql/scalar-functions#lpad)
- [ltrim](https://spice.ai/docs/sql/scalar-functions#ltrim)
- [octet\_length](https://spice.ai/docs/sql/scalar-functions#octet_length)
- [overlay](https://spice.ai/docs/sql/scalar-functions#overlay)
- [parse\_url](https://spice.ai/docs/sql/scalar-functions#parse_url)
- [position](https://spice.ai/docs/sql/scalar-functions#position)
- [repeat](https://spice.ai/docs/sql/scalar-functions#repeat)
- [replace](https://spice.ai/docs/sql/scalar-functions#replace)
- [reverse](https://spice.ai/docs/sql/scalar-functions#reverse)
- [right](https://spice.ai/docs/sql/scalar-functions#right)
- [rpad](https://spice.ai/docs/sql/scalar-functions#rpad)
- [rtrim](https://spice.ai/docs/sql/scalar-functions#rtrim)
- [split\_part](https://spice.ai/docs/sql/scalar-functions#split_part)
- [starts\_with](https://spice.ai/docs/sql/scalar-functions#starts_with)
- [strpos](https://spice.ai/docs/sql/scalar-functions#strpos)
- [substr](https://spice.ai/docs/sql/scalar-functions#substr)
- [substr\_index](https://spice.ai/docs/sql/scalar-functions#substr_index)
- [substring](https://spice.ai/docs/sql/scalar-functions#substring)
- [substring\_index](https://spice.ai/docs/sql/scalar-functions#substring_index)
- [to\_hex](https://spice.ai/docs/sql/scalar-functions#to_hex)
- [translate](https://spice.ai/docs/sql/scalar-functions#translate)
- [trim](https://spice.ai/docs/sql/scalar-functions#trim)
- [upper](https://spice.ai/docs/sql/scalar-functions#upper)
- [uuid](https://spice.ai/docs/sql/scalar-functions#uuid)

### `ascii`

Returns the Unicode code point of the first character in a string. If the string is empty, returns 0.

```sql
ascii(str)
```

#### Arguments

- **str**: String expression. Accepts constants, columns, or expressions.

#### Example

```sql
> select ascii('abc');
+--------------------+
| ascii(Utf8("abc")) |
+--------------------+
| 97                 |
+--------------------+
> select ascii('🚀');
+-------------------+
| ascii(Utf8("🚀")) |
+-------------------+
| 128640            |
+-------------------+
```

Related function: [`chr`](https://spice.ai/docs/sql/scalar-functions#chr)

### `bit_length`

Returns the number of bits in the string. Each character is counted according to its byte representation (8 bits per byte).

```sql
bit_length(str)
```

#### Arguments

- **str**: String expression.

#### Example

```sql
> select bit_length('datafusion');
+--------------------------------+
| bit_length(Utf8("datafusion")) |
+--------------------------------+
| 80                             |
+--------------------------------+
```

Related functions: [`length`](https://spice.ai/docs/sql/scalar-functions#length), [`octet_length`](https://spice.ai/docs/sql/scalar-functions#octet_length)

### `btrim`

Removes the longest string containing only characters in `trim_str` from the start and end of `str`. If `trim_str` is omitted, whitespace is removed.

```sql
btrim(str[, trim_str])
```

#### Arguments

- **str**: String expression.
- **trim\_str**: Optional string of characters to trim. Defaults to whitespace.

#### Example

```sql
> select btrim('__datafusion____', '_');
+-------------------------------------------+
| btrim(Utf8("__datafusion____"),Utf8("_")) |
+-------------------------------------------+
| datafusion                                |
+-------------------------------------------+
```

Alternative syntax: `trim(BOTH trim_str FROM str)` or `trim(trim_str FROM str)`

Aliases: `trim`

Related functions: [`ltrim`](https://spice.ai/docs/sql/scalar-functions#ltrim), [`rtrim`](https://spice.ai/docs/sql/scalar-functions#rtrim)

### `char_length`

Alias of [`character_length`](https://spice.ai/docs/sql/scalar-functions#character_length).

### `character_length`

Returns the number of characters in a string, not bytes. Handles Unicode correctly.

```sql
character_length(str)
```

#### Arguments

- **str**: String expression.

#### Example

```sql
> select character_length('Ångström');
+------------------------------------+
| character_length(Utf8("Ångström")) |
+------------------------------------+
| 8                                  |
+------------------------------------+
```

Aliases: `length`, `char_length`

Related functions: [`bit_length`](https://spice.ai/docs/sql/scalar-functions#bit_length), [`octet_length`](https://spice.ai/docs/sql/scalar-functions#octet_length)

### `chr`

Returns the character with the specified Unicode code point.

```sql
chr(expression)
```

#### Arguments

- **expression**: Integer code point.

#### Example

```sql
> select chr(128640);
+--------------------+
| chr(Int64(128640)) |
+--------------------+
| 🚀                 |
+--------------------+
```

Related function: [`ascii`](https://spice.ai/docs/sql/scalar-functions#ascii)

### `concat`

Concatenates two or more strings into a single string.

```sql
concat(str[, ..., str_n])
```

#### Arguments

- **str**: String expression.
- **str\_n**: Additional string expressions.

#### Example

```sql
> select concat('data', 'f', 'us', 'ion');
+-------------------------------------------------------+
| concat(Utf8("data"),Utf8("f"),Utf8("us"),Utf8("ion")) |
+-------------------------------------------------------+
| datafusion                                            |
+-------------------------------------------------------+
```

Related function: [`concat_ws`](https://spice.ai/docs/sql/scalar-functions#concat_ws)

### `concat_ws`

Concatenates strings using a separator between each value.

```sql
concat_ws(separator, str[, ..., str_n])
```

#### Arguments

- **separator**: String separator.
- **str**: String expression.
- **str\_n**: Additional string expressions.

#### Example

```sql
> select concat_ws('_', 'data', 'fusion');
+--------------------------------------------------+
| concat_ws(Utf8("_"),Utf8("data"),Utf8("fusion")) |
+--------------------------------------------------+
| data_fusion                                      |
+--------------------------------------------------+
```

Related function: [`concat`](https://spice.ai/docs/sql/scalar-functions#concat)

### `contains`

Returns true if `search_str` is found within `str`. The search is case-sensitive.

```sql
contains(str, search_str)
```

#### Arguments

- **str**: String expression.
- **search\_str**: Substring to search for.

#### Example

```sql
> select contains('the quick brown fox', 'row');
+---------------------------------------------------+
| contains(Utf8("the quick brown fox"),Utf8("row")) |
+---------------------------------------------------+
| true                                              |
+---------------------------------------------------+
```

### `like`

Performs SQL pattern matching using `%` to match zero or more characters and `_` to match a single character. The optional SQL `ESCAPE` clause can be used to treat a wildcard literally, matching Spark SQL behavior.

```sql
like(str, pattern)
```

#### Arguments

- **str**: String expression to compare.
- **pattern**: Pattern containing literal text plus `%` and `_` wildcards.

#### Example

```sql
> select like('spice.ai', 'spice%');
+----------------------------------+
| like(Utf8("spice.ai"),Utf8("spice%")) |
+----------------------------------+
| true                             |
+----------------------------------+
```

Reference: [Spark SQL `like`](https://spark.apache.org/docs/latest/api/sql/index.html#like).

### `ilike`

Case-insensitive variant of [`like`](https://spice.ai/docs/sql/scalar-functions#like) that treats ASCII characters in `str` and `pattern` without regard to case. The optional SQL `ESCAPE` clause may be used to treat `%` or `_` literally.

```sql
ilike(str, pattern)
```

#### Arguments

- **str**: String expression to compare.
- **pattern**: Case-insensitive pattern containing literal text plus `%` and `_` wildcards.

#### Example

```sql
> select ilike('Spice.AI', 'spice%');
+-----------------------------------+
| ilike(Utf8("Spice.AI"),Utf8("spice%")) |
+-----------------------------------+
| true                              |
+-----------------------------------+
```

Reference: [Spark SQL `ilike`](https://spark.apache.org/docs/latest/api/sql/index.html#ilike).

### `ends_with`

Returns true if `str` ends with the substring `substr`.

```sql
ends_with(str, substr)
```

#### Arguments

- **str**: String expression.
- **substr**: Substring to test for.

#### Example

```sql
> select ends_with('datafusion', 'soin');
+--------------------------------------------+
| ends_with(Utf8("datafusion"),Utf8("soin")) |
+--------------------------------------------+
| false                                      |
+--------------------------------------------+
> select ends_with('datafusion', 'sion');
+--------------------------------------------+
| ends_with(Utf8("datafusion"),Utf8("sion")) |
+--------------------------------------------+
| true                                       |
+--------------------------------------------+
```

### `find_in_set`

Returns the position (1-based) of `str` in the comma-separated list `strlist`. Returns 0 if not found.

```sql
find_in_set(str, strlist)
```

#### Arguments

- **str**: String to find.
- **strlist**: Comma-separated list of substrings.

#### Example

```sql
> select find_in_set('b', 'a,b,c,d');
+----------------------------------------+
| find_in_set(Utf8("b"),Utf8("a,b,c,d")) |
+----------------------------------------+
| 2                                      |
+----------------------------------------+
```

### `initcap`

Capitalizes the first character of each word in the string. Words are delimited by non-alphanumeric characters.

```sql
initcap(str)
```

#### Arguments

- **str**: String expression.

#### Example

```sql
> select initcap('apache datafusion');
+------------------------------------+
| initcap(Utf8("apache datafusion")) |
+------------------------------------+
| Apache Datafusion                  |
+------------------------------------+
```

Related functions: [`lower`](https://spice.ai/docs/sql/scalar-functions#lower), [`upper`](https://spice.ai/docs/sql/scalar-functions#upper)

### `instr`

Alias of [`strpos`](https://spice.ai/docs/sql/scalar-functions#strpos).

### `left`

Returns the first `n` characters from the left side of the string.

```sql
left(str, n)
```

#### Arguments

- **str**: String expression.
- **n**: Number of characters to return.

#### Example

```sql
> select left('datafusion', 4);
+-----------------------------------+
| left(Utf8("datafusion"),Int64(4)) |
+-----------------------------------+
| data                              |
+-----------------------------------+
```

Related function: [`right`](https://spice.ai/docs/sql/scalar-functions#right)

### `length`

Alias of [`character_length`](https://spice.ai/docs/sql/scalar-functions#character_length).

### `levenshtein`

Returns the [Levenshtein distance](https://en.wikipedia.org/wiki/Levenshtein_distance) between two strings.

```sql
levenshtein(str1, str2)
```

#### Arguments

- **str1**: First string.
- **str2**: Second string.

#### Example

```sql
> select levenshtein('kitten', 'sitting');
+---------------------------------------------+
| levenshtein(Utf8("kitten"),Utf8("sitting")) |
+---------------------------------------------+
| 3                                           |
+---------------------------------------------+
```

### `lower`

Converts all characters in the string to lower case.

```sql
lower(str)
```

#### Arguments

- **str**: String expression.

#### Example

```sql
> select lower('Ångström');
+-------------------------+
| lower(Utf8("Ångström")) |
+-------------------------+
| ångström                |
+-------------------------+
```

Related functions: [`initcap`](https://spice.ai/docs/sql/scalar-functions#initcap), [`upper`](https://spice.ai/docs/sql/scalar-functions#upper)

### `luhn_check`

Validates that a string of digits satisfies the [Luhn checksum](https://en.wikipedia.org/wiki/Luhn_algorithm), returning true for valid numbers and false otherwise. This matches the Spark SQL implementation and is useful for validating identifiers such as credit card numbers.

```sql
luhn_check(str)
```

#### Arguments

- **str**: String expression containing digits.

#### Example

```sql
> select luhn_check('79927398713');
+--------------------------------------+
| luhn_check(Utf8("79927398713"))      |
+--------------------------------------+
| true                                 |
+--------------------------------------+
```

Reference: [Spark SQL `luhn_check`](https://spark.apache.org/docs/latest/api/sql/index.html#luhn_check).

### `lpad`

Pads the left side of the string with another string until the result reaches the specified length. If the padding string is omitted, a space is used.

```sql
lpad(str, n[, padding_str])
```

#### Arguments

- **str**: String expression.
- **n**: Target length.
- **padding\_str**: Optional string to pad with.

#### Example

```sql
> select lpad('Dolly', 10, 'hello');
+---------------------------------------------+
| lpad(Utf8("Dolly"),Int64(10),Utf8("hello")) |
+---------------------------------------------+
| helloDolly                                  |
+---------------------------------------------+
```

Related function: [`rpad`](https://spice.ai/docs/sql/scalar-functions#rpad)

### `ltrim`

Removes the longest string containing only characters in `trim_str` from the start of `str`. If `trim_str` is omitted, whitespace is removed.

```sql
ltrim(str[, trim_str])
```

#### Arguments

- **str**: String expression.
- **trim\_str**: Optional string of characters to trim. Defaults to whitespace.

#### Example

```sql
> select ltrim('  datafusion  ');
+-------------------------------+
| ltrim(Utf8("  datafusion  ")) |
+-------------------------------+
| datafusion                    |
+-------------------------------+
> select ltrim('___datafusion___', '_');
+-------------------------------------------+
| ltrim(Utf8("___datafusion___"),Utf8("_")) |
+-------------------------------------------+
| datafusion___                             |
+-------------------------------------------+
```

Alternative syntax: `trim(LEADING trim_str FROM str)`

Related functions: [`btrim`](https://spice.ai/docs/sql/scalar-functions#btrim), [`rtrim`](https://spice.ai/docs/sql/scalar-functions#rtrim)

### `octet_length`

Returns the number of bytes in the string.

```sql
octet_length(str)
```

#### Arguments

- **str**: String expression.

#### Example

```sql
> select octet_length('Ångström');
+--------------------------------+
| octet_length(Utf8("Ångström")) |
+--------------------------------+
| 10                             |
+--------------------------------+
```

Related functions: [`bit_length`](https://spice.ai/docs/sql/scalar-functions#bit_length), [`length`](https://spice.ai/docs/sql/scalar-functions#length)

### `overlay`

Replaces a substring of `str` with `substr`, starting at position `pos` for `count` characters. If `count` is omitted, uses the length of `substr`.

```sql
overlay(str PLACING substr FROM pos [FOR count])
```

#### Arguments

- **str**: String expression.
- **substr**: Replacement string.
- **pos**: Start position (1-based).
- **count**: Optional number of characters to replace.

#### Example

```sql
> select overlay('Txxxxas' placing 'hom' from 2 for 4);
+--------------------------------------------------------+
| overlay(Utf8("Txxxxas"),Utf8("hom"),Int64(2),Int64(4)) |
+--------------------------------------------------------+
| Thomas                                                 |
+--------------------------------------------------------+
```

### `parse_url`

Extracts a component from a URL, or retrieves an individual query parameter when provided a key, following Spark SQL semantics. Supported parts include `HOST`, `PATH`, `QUERY`, `REF`, `PROTOCOL`, `FILE`, and `AUTHORITY`.

```sql
parse_url(url, part_to_extract[, key])
```

#### Arguments

- **url**: URL string expression.
- **part\_to\_extract**: Case-insensitive token identifying which component to extract.
- **key**: Optional query parameter key to extract from the `QUERY` part.

#### Example

```sql
> select parse_url('https://spice.ai/blog?id=42', 'HOST');
+-------------------------------------------------------+
| parse_url(Utf8("https://spice.ai/blog?id=42"),Utf8("HOST")) |
+-------------------------------------------------------+
| spice.ai                                              |
+-------------------------------------------------------+
> select parse_url('https://spice.ai/blog?id=42', 'QUERY', 'id');
+------------------------------------------------------------------+
| parse_url(Utf8("https://spice.ai/blog?id=42"),Utf8("QUERY"),Utf8("id")) |
+------------------------------------------------------------------+
| 42                                                               |
+------------------------------------------------------------------+
```

Reference: [Spark SQL `parse_url`](https://spark.apache.org/docs/latest/api/sql/index.html#parse_url).

### `position`

Alias of [`strpos`](https://spice.ai/docs/sql/scalar-functions#strpos).

### `repeat`

Returns a string consisting of the input string repeated `n` times.

```sql
repeat(str, n)
```

#### Arguments

- **str**: String expression.
- **n**: Number of repetitions.

#### Example

```sql
> select repeat('data', 3);
+-------------------------------+
| repeat(Utf8("data"),Int64(3)) |
+-------------------------------+
| datadatadata                  |
+-------------------------------+
```

### `replace`

Replaces all occurrences of `substr` in `str` with `replacement`.

```sql
replace(str, substr, replacement)
```

#### Arguments

- **str**: String expression.
- **substr**: Substring to replace.
- **replacement**: Replacement string.

#### Example

```sql
> select replace('ABabbaBA', 'ab', 'cd');
+-------------------------------------------------+
| replace(Utf8("ABabbaBA"),Utf8("ab"),Utf8("cd")) |
+-------------------------------------------------+
| ABcdbaBA                                        |
+-------------------------------------------------+
```

### `reverse`

Returns the string with the character order reversed.

```sql
reverse(str)
```

#### Arguments

- **str**: String expression.

#### Example

```sql
> select reverse('datafusion');
+-----------------------------+
| reverse(Utf8("datafusion")) |
+-----------------------------+
| noisufatad                  |
+-----------------------------+
```

### `right`

Returns the last `n` characters from the right side of the string.

```sql
right(str, n)
```

#### Arguments

- **str**: String expression.
- **n**: Number of characters to return.

#### Example

```sql
> select right('datafusion', 6);
+------------------------------------+
| right(Utf8("datafusion"),Int64(6)) |
+------------------------------------+
| fusion                             |
+------------------------------------+
```

Related function: [`left`](https://spice.ai/docs/sql/scalar-functions#left)

### `rpad`

Pads the right side of the string with another string until the result reaches the specified length. If the padding string is omitted, a space is used.

```sql
rpad(str, n[, padding_str])
```

#### Arguments

- **str**: String expression.
- **n**: Target length.
- **padding\_str**: Optional string to pad with.

#### Example

```sql
> select rpad('datafusion', 20, '_-');
+-----------------------------------------------+
| rpad(Utf8("datafusion"),Int64(20),Utf8("_-")) |
+-----------------------------------------------+
| datafusion_-_-_-_-_-                          |
+-----------------------------------------------+
```

Related function: [`lpad`](https://spice.ai/docs/sql/scalar-functions#lpad)

### `rtrim`

Removes the longest string containing only characters in `trim_str` from the end of `str`. If `trim_str` is omitted, whitespace is removed.

```sql
rtrim(str[, trim_str])
```

#### Arguments

- **str**: String expression.
- **trim\_str**: Optional string of characters to trim. Defaults to whitespace.

#### Example

```sql
> select rtrim('  datafusion  ');
+-------------------------------+
| rtrim(Utf8("  datafusion  ")) |
+-------------------------------+
|   datafusion                  |
+-------------------------------+
> select rtrim('___datafusion___', '_');
+-------------------------------------------+
| rtrim(Utf8("___datafusion___"),Utf8("_")) |
+-------------------------------------------+
| ___datafusion                             |
+-------------------------------------------+
```

Alternative syntax: `trim(TRAILING trim_str FROM str)`

Related functions: [`btrim`](https://spice.ai/docs/sql/scalar-functions#btrim), [`ltrim`](https://spice.ai/docs/sql/scalar-functions#ltrim)

### `split_part`

Splits the string on the specified delimiter and returns the substring at the given position (1-based).

```sql
split_part(str, delimiter, pos)
```

#### Arguments

- **str**: String expression.
- **delimiter**: Delimiter string.
- **pos**: Position of the part to return (1-based).

#### Example

```sql
> select split_part('1.2.3.4.5', '.', 3);
+--------------------------------------------------+
| split_part(Utf8("1.2.3.4.5"),Utf8("."),Int64(3)) |
+--------------------------------------------------+
| 3                                                |
+--------------------------------------------------+
```

### `starts_with`

Returns true if `str` starts with the substring `substr`.

```sql
starts_with(str, substr)
```

#### Arguments

- **str**: String expression.
- **substr**: Substring to test for.

#### Example

```sql
> select starts_with('datafusion','data');
+----------------------------------------------+
| starts_with(Utf8("datafusion"),Utf8("data")) |
+----------------------------------------------+
| true                                         |
+----------------------------------------------+
```

### `strpos`

Returns the position (1-based) of the first occurrence of `substr` in `str`. Returns 0 if not found.

```sql
strpos(str, substr)
```

#### Arguments

- **str**: String expression.
- **substr**: Substring to search for.

#### Example

```sql
> select strpos('datafusion', 'fus');
+----------------------------------------+
| strpos(Utf8("datafusion"),Utf8("fus")) |
+----------------------------------------+
| 5                                      |
+----------------------------------------+
```

Alternative syntax: `position(substr in origstr)`

Aliases: `instr`, `position`

### `substr`

Extracts a substring from `str`, starting at `start_pos` for `length` characters. If `length` is omitted, returns the rest of the string.

```sql
substr(str, start_pos[, length])
```

#### Arguments

- **str**: String expression.
- **start\_pos**: Start position (1-based).
- **length**: Optional number of characters to extract.

#### Example

```sql
> select substr('datafusion', 5, 3);
+----------------------------------------------+
| substr(Utf8("datafusion"),Int64(5),Int64(3)) |
+----------------------------------------------+
| fus                                          |
+----------------------------------------------+
```

Alternative syntax: `substring(str from start_pos for length)`

Aliases: `substring`

### `substr_index`

Returns the substring from `str` before or after a specified number of occurrences of the delimiter `delim`. If `count` is positive, returns everything to the left of the final delimiter (counting from the left). If `count` is negative, returns everything to the right of the final delimiter (counting from the right).

```sql
substr_index(str, delim, count)
```

#### Arguments

- **str**: String expression.
- **delim**: Delimiter string.
- **count**: Number of occurrences (positive or negative).

#### Example

```sql
> select substr_index('www.apache.org', '.', 1);
+---------------------------------------------------------+
| substr_index(Utf8("www.apache.org"),Utf8("."),Int64(1)) |
+---------------------------------------------------------+
| www                                                     |
+---------------------------------------------------------+
> select substr_index('www.apache.org', '.', -1);
+----------------------------------------------------------+
| substr_index(Utf8("www.apache.org"),Utf8("."),Int64(-1)) |
+----------------------------------------------------------+
| org                                                      |
+----------------------------------------------------------+
```

Aliases: `substring_index`

### `substring`

Alias of [`substr`](https://spice.ai/docs/sql/scalar-functions#substr).

### `substring_index`

Alias of [`substr_index`](https://spice.ai/docs/sql/scalar-functions#substr_index).

### `to_hex`

Converts an integer to its hexadecimal string representation.

```sql
to_hex(int)
```

#### Arguments

- **int**: Integer expression.

#### Example

```sql
> select to_hex(12345689);
+-------------------------+
| to_hex(Int64(12345689)) |
+-------------------------+
| bc6159                  |
+-------------------------+
```

### `translate`

Replaces each character in `str` that matches a character in `chars` with the corresponding character in `translation`. If `translation` is shorter than `chars`, extra characters are removed.

```sql
translate(str, chars, translation)
```

#### Arguments

- **str**: String expression.
- **chars**: Characters to translate.
- **translation**: Replacement characters.

#### Example

```sql
> select translate('twice', 'wic', 'her');
+--------------------------------------------------+
| translate(Utf8("twice"),Utf8("wic"),Utf8("her")) |
+--------------------------------------------------+
| there                                            |
+--------------------------------------------------+
```

### `trim`

Alias of [`btrim`](https://spice.ai/docs/sql/scalar-functions#btrim).

### `upper`

Converts all characters in the string to upper case.

```sql
upper(str)
```

#### Arguments

- **str**: String expression.

#### Example

```sql
> select upper('dataFusion');
+---------------------------+
| upper(Utf8("dataFusion")) |
+---------------------------+
| DATAFUSION                |
+---------------------------+
```

Related functions: [`initcap`](https://spice.ai/docs/sql/scalar-functions#initcap), [`lower`](https://spice.ai/docs/sql/scalar-functions#lower)

### `uuid`

Returns a [UUID v4](https://en.wikipedia.org/wiki/Universally_unique_identifier#Version_4_\(random\)) string value that is unique per row.

```sql
uuid()
```

#### Example

```sql
> select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 6ec17ef8-1934-41cc-8d59-d0c8f9eea1f0 |
+--------------------------------------+
```

---

## Binary String Functions

Binary string functions help encode and decode binary data, such as base64 and hexadecimal conversions. These are useful for working with encoded data or binary blobs.

- [bit\_get](https://spice.ai/docs/sql/scalar-functions#bit_get)
- [bit\_count](https://spice.ai/docs/sql/scalar-functions#bit_count)
- [bitmap\_count](https://spice.ai/docs/sql/scalar-functions#bitmap_count)

### `bit_get`

Returns the bit (0 or 1) at the specified zero-based position when counting from the least-significant bit of an integral or binary expression, matching Spark SQL semantics.

```sql
bit_get(value, position)
```

#### Arguments

- **value**: Integer or binary expression whose bits are inspected.
- **position**: Zero-based index of the bit to return. Must be non-negative.

#### Example

```sql
> select bit_get(11, 2) as bit;
+-----+
| bit |
+-----+
| 0   |
+-----+
```

Reference: [Spark SQL `bit_get`](https://spark.apache.org/docs/latest/api/sql/index.html#bit_get).

### `bit_count`

Counts the number of set bits in an integral or binary expression. Useful for quick popcount operations on bitmaps or packed flags, aligned with Spark SQL behavior.

```sql
bit_count(value)
```

#### Arguments

- **value**: Integer or binary expression.

#### Example

```sql
> select bit_count(255) as popcnt;
+--------+
| popcnt |
+--------+
| 8      |
+--------+
```

Reference: [Spark SQL `bit_count`](https://spark.apache.org/docs/latest/api/sql/index.html#bit_count).

### `bitmap_count`

Returns the number of set bits in a binary bitmap produced by functions such as `bitmap_construct_agg`, mirroring the Spark SQL implementation.

```sql
bitmap_count(bitmap)
```

#### Arguments

- **bitmap**: Binary expression representing a bitmap.

#### Example

```sql
> select bitmap_count(x'0F') as popcnt;
+--------+
| popcnt |
+--------+
| 4      |
+--------+
```

Reference: [Spark SQL `bitmap_count`](https://spark.apache.org/docs/latest/api/sql/index.html#bitmap_count).

## Regular Expression Functions

Regular expression functions help match, extract, and replace patterns in strings. Spice.ai uses a PCRE-like regular expression syntax. Spice supports the following regular expressions:

- [regexp\_like](https://spice.ai/docs/sql/scalar-functions#regexp_like)
- [regexp\_match](https://spice.ai/docs/sql/scalar-functions#regexp_match)
- [regexp\_replace](https://spice.ai/docs/sql/scalar-functions#regexp_replace)
- [regexp\_count](https://spice.ai/docs/sql/scalar-functions#regexp_count)
- [regexp\_instr](https://spice.ai/docs/sql/scalar-functions#regexp_instr)

### `regexp_like`

Returns true if a regular expression has at least one match in a string, false otherwise.

```sql
regexp_like(str, regexp[, flags])
```

#### Arguments

- **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\*?

#### Example

```sql
> select regexp_like('Köln', '[a-zA-Z]ö[a-zA-Z]{2}');
+--------------------------------------------------------+
| regexp_like(Utf8("Köln"),Utf8("[a-zA-Z]ö[a-zA-Z]{2}")) |
+--------------------------------------------------------+
| true                                                   |
+--------------------------------------------------------+
> SELECT regexp_like('aBc', '(b|d)', 'i');
+--------------------------------------------------+
| regexp_like(Utf8("aBc"),Utf8("(b|d)"),Utf8("i")) |
+--------------------------------------------------+
| true                                             |
+--------------------------------------------------+
```

### `regexp_match`

Returns the first regular expression matches in a string.

```sql
regexp_match(str, regexp[, flags])
```

#### Arguments

- **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\*?

#### Example

```sql
> select regexp_match('Köln', '[a-zA-Z]ö[a-zA-Z]{2}');
+---------------------------------------------------------+
| regexp_match(Utf8("Köln"),Utf8("[a-zA-Z]ö[a-zA-Z]{2}")) |
+---------------------------------------------------------+
| [Köln]                                                  |
+---------------------------------------------------------+
SELECT regexp_match('aBc', '(b|d)', 'i');
+---------------------------------------------------+
| regexp_match(Utf8("aBc"),Utf8("(b|d)"),Utf8("i")) |
+---------------------------------------------------+
| [B]                                               |
+---------------------------------------------------+
```

### `regexp_replace`

Replaces substrings in a string that match a regular expression.

```sql
regexp_replace(str, regexp, replacement[, flags])
```

#### Arguments

- **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\*?

#### Example

```sql
> select regexp_replace('foobarbaz', 'b(..)', 'X\\1Y', 'g');
+------------------------------------------------------------------------+
| regexp_replace(Utf8("foobarbaz"),Utf8("b(..)"),Utf8("X\1Y"),Utf8("g")) |
+------------------------------------------------------------------------+
| fooXarYXazY                                                            |
+------------------------------------------------------------------------+
SELECT regexp_replace('aBc', '(b|d)', 'Ab\\1a', 'i');
+-------------------------------------------------------------------+
| regexp_replace(Utf8("aBc"),Utf8("(b|d)"),Utf8("Ab\1a"),Utf8("i")) |
+-------------------------------------------------------------------+
| aAbBac                                                            |
+-------------------------------------------------------------------+
```

### `regexp_count`

Returns the number of matches that a regular expression has in a string. Returns `NULL` when `str`, `regexp`, `start`, or `flags` is `NULL`.

```sql
regexp_count(str, regexp[, start, flags])
```

#### Arguments

- **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**: 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\*?

#### Example

```sql
> select regexp_count('abcAbAbc', 'abc', 2, 'i');
+---------------------------------------------------------------+
| regexp_count(Utf8("abcAbAbc"),Utf8("abc"),Int64(2),Utf8("i")) |
+---------------------------------------------------------------+
| 1                                                             |
+---------------------------------------------------------------+
```

### `regexp_instr`

Returns the position in a string where the specified occurrence of a POSIX regular expression is located.

```sql
regexp_instr(str, regexp[, start[, N[, flags[, subexpr]]]])
```

#### Arguments

- **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**: 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**: 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.

#### Example

```sql
> SELECT regexp_instr('ABCDEF', 'C(.)(..)');
+---------------------------------------------------------------+
| regexp_instr(Utf8("ABCDEF"),Utf8("C(.)(..)"))                 |
+---------------------------------------------------------------+
| 3                                                             |
+---------------------------------------------------------------+
```

## Time and Date Functions

Time and date functions help extract, format, and manipulate temporal data. Functions include `current_date`, `now`, `date_part`, `date_trunc`, and various conversion functions. These are essential for time series analysis and working with timestamps.

- [current\_date](https://spice.ai/docs/sql/scalar-functions#current_date)
- [current\_time](https://spice.ai/docs/sql/scalar-functions#current_time)
- [current\_timestamp](https://spice.ai/docs/sql/scalar-functions#current_timestamp)
- [date\_bin](https://spice.ai/docs/sql/scalar-functions#date_bin)
- [date\_add](https://spice.ai/docs/sql/scalar-functions#date_add)
- [date\_sub](https://spice.ai/docs/sql/scalar-functions#date_sub)
- [last\_day](https://spice.ai/docs/sql/scalar-functions#last_day)
- [next\_day](https://spice.ai/docs/sql/scalar-functions#next_day)
- [date\_format](https://spice.ai/docs/sql/scalar-functions#date_format)
- [date\_part](https://spice.ai/docs/sql/scalar-functions#date_part)
- [date\_trunc](https://spice.ai/docs/sql/scalar-functions#date_trunc)
- [datepart](https://spice.ai/docs/sql/scalar-functions#datepart)
- [datetrunc](https://spice.ai/docs/sql/scalar-functions#datetrunc)
- [from\_unixtime](https://spice.ai/docs/sql/scalar-functions#from_unixtime)
- [make\_date](https://spice.ai/docs/sql/scalar-functions#make_date)
- [now](https://spice.ai/docs/sql/scalar-functions#now)
- [to\_char](https://spice.ai/docs/sql/scalar-functions#to_char)
- [to\_date](https://spice.ai/docs/sql/scalar-functions#to_date)
- [to\_local\_time](https://spice.ai/docs/sql/scalar-functions#to_local_time)
- [to\_timestamp](https://spice.ai/docs/sql/scalar-functions#to_timestamp)
- [to\_timestamp\_micros](https://spice.ai/docs/sql/scalar-functions#to_timestamp_micros)
- [to\_timestamp\_millis](https://spice.ai/docs/sql/scalar-functions#to_timestamp_millis)
- [to\_timestamp\_nanos](https://spice.ai/docs/sql/scalar-functions#to_timestamp_nanos)
- [to\_timestamp\_seconds](https://spice.ai/docs/sql/scalar-functions#to_timestamp_seconds)
- [to\_unixtime](https://spice.ai/docs/sql/scalar-functions#to_unixtime)
- [today](https://spice.ai/docs/sql/scalar-functions#today)

### `current_date`

Returns the current UTC date.

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.

```sql
current_date()
```

#### Aliases

- today

### `current_time`

Returns the current UTC time.

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.

```sql
current_time()
```

### `current_timestamp`

_Alias of [now](https://spice.ai/docs/sql/scalar-functions#now)._

### `date_bin`

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

```sql
date_bin(interval, expression, origin-timestamp)
```

#### Arguments

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

#### Example

```sql
-- Bin the timestamp into 1 day intervals
> SELECT date_bin(interval '1 day', time) as bin
FROM VALUES ('2023-01-01T18:18:18Z'), ('2023-01-03T19:00:03Z')  t(time);
+---------------------+
| bin                 |
+---------------------+
| 2023-01-01T00:00:00 |
| 2023-01-03T00:00:00 |
+---------------------+
2 row(s) fetched.

-- Bin the timestamp into 1 day intervals starting at 3AM on  2023-01-01
> SELECT date_bin(interval '1 day', time,  '2023-01-01T03:00:00') as bin
FROM VALUES ('2023-01-01T18:18:18Z'), ('2023-01-03T19:00:03Z')  t(time);
+---------------------+
| bin                 |
+---------------------+
| 2023-01-01T03:00:00 |
| 2023-01-03T03:00:00 |
+---------------------+
2 row(s) fetched.
```

### `date_add`

Adds a number of days to a DATE or TIMESTAMP expression, matching Spark SQL semantics. Negative offsets move backwards in time.

```sql
date_add(start_date, num_days)
```

#### Arguments

- **start\_date**: DATE or TIMESTAMP expression.
- **num\_days**: Integer number of days to add.

#### Example

```sql
> select date_add(date '2024-02-27', 3);
+---------------------------------+
| date_add(Date32("2024-02-27"),Int64(3)) |
+---------------------------------+
| 2024-03-01                     |
+---------------------------------+
```

Reference: [Spark SQL `date_add`](https://spark.apache.org/docs/latest/api/sql/index.html#date_add).

### `date_sub`

Subtracts a number of days from a DATE or TIMESTAMP expression using Spark-compatible behavior.

```sql
date_sub(start_date, num_days)
```

#### Arguments

- **start\_date**: DATE or TIMESTAMP expression.
- **num\_days**: Integer number of days to subtract.

#### Example

```sql
> select date_sub(date '2024-03-05', 7);
+---------------------------------+
| date_sub(Date32("2024-03-05"),Int64(7)) |
+---------------------------------+
| 2024-02-27                     |
+---------------------------------+
```

Reference: [Spark SQL `date_sub`](https://spark.apache.org/docs/latest/api/sql/index.html#date_sub).

### `last_day`

Returns the last day of the month that contains the input date or timestamp, matching Spark SQL semantics.

```sql
last_day(expression)
```

#### Arguments

- **expression**: DATE or TIMESTAMP expression.

#### Example

```sql
> select last_day(date '2024-02-14');
+----------------------------------+
| last_day(Date32("2024-02-14"))   |
+----------------------------------+
| 2024-02-29                      |
+----------------------------------+
```

Reference: [Spark SQL `last_day`](https://spark.apache.org/docs/latest/api/sql/index.html#last_day).

### `next_day`

Returns the first date after `start_date` that matches the requested day of week. Valid day names include full names (e.g., `Monday`) or abbreviations such as `Mon`, matching Spark SQL behavior.

```sql
next_day(start_date, day_of_week)
```

#### Arguments

- **start\_date**: DATE or TIMESTAMP expression.
- **day\_of\_week**: String literal naming the target weekday.

#### Example

```sql
> select next_day(date '2024-02-14', 'FRI');
+--------------------------------------------------+
| next_day(Date32("2024-02-14"),Utf8("FRI"))        |
+--------------------------------------------------+
| 2024-02-16                                       |
+--------------------------------------------------+
```

Reference: [Spark SQL `next_day`](https://spark.apache.org/docs/latest/api/sql/index.html#next_day).

### `date_format`

_Alias of [to\_char](https://spice.ai/docs/sql/scalar-functions#to_char)._

### `date_part`

Returns the specified part of the date as an integer.

```sql
date_part(part, expression)
```

#### Arguments

- **part**: Part of the date to return. The following date parts are supported:

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

#### Alternative Syntax

```sql
extract(field FROM source)
```

`date_part` and `extract` return the same value for every field. For `dow`, both count Sunday as `0`:

```sql
> select date_part('dow', DATE '2024-01-07') AS date_part_dow, extract(dow FROM DATE '2024-01-07') AS extract_dow;
+---------------+-------------+
| date_part_dow | extract_dow |
+---------------+-------------+
| 0             | 0           |
+---------------+-------------+
```

#### Aliases

- datepart

### `date_trunc`

Truncates a timestamp value to a specified precision.

```sql
date_trunc(precision, expression)
```

#### Arguments

- **precision**: Time precision to truncate to. The following precisions are supported:

  - year / YEAR
  - quarter / QUARTER
  - month / MONTH
  - week / WEEK
  - day / DAY
  - hour / HOUR
  - minute / MINUTE
  - second / SECOND
  - millisecond / MILLISECOND
  - microsecond / MICROSECOND

- **expression**: Time expression to operate on. Can be a constant, column, or function.

#### Aliases

- datetrunc

### `datepart`

_Alias of [date\_part](https://spice.ai/docs/sql/scalar-functions#date_part)._

### `datetrunc`

_Alias of [date\_trunc](https://spice.ai/docs/sql/scalar-functions#date_trunc)._

### `from_unixtime`

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.

```sql
from_unixtime(expression[, timezone])
```

#### Arguments

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

#### Example

```sql
> select from_unixtime(1599572549, 'America/New_York');
+-----------------------------------------------------------+
| from_unixtime(Int64(1599572549),Utf8("America/New_York")) |
+-----------------------------------------------------------+
| 2020-09-08T09:42:29-04:00                                 |
+-----------------------------------------------------------+
```

### `make_date`

Make a date from year/month/day component parts.

```sql
make_date(year, month, day)
```

#### Arguments

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

#### Example

```sql
> select make_date(2023, 1, 31);
+-------------------------------------------+
| make_date(Int64(2023),Int64(1),Int64(31)) |
+-------------------------------------------+
| 2023-01-31                                |
+-------------------------------------------+
> select make_date('2023', '01', '31');
+-----------------------------------------------+
| make_date(Utf8("2023"),Utf8("01"),Utf8("31")) |
+-----------------------------------------------+
| 2023-01-31                                    |
+-----------------------------------------------+
```

### `now`

Returns the current UTC timestamp.

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.

```sql
now()
```

#### Aliases

- current\_timestamp

### `to_char`

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.

```sql
to_char(expression, format)
```

#### Arguments

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

#### Example

```sql
> select to_char('2023-03-01'::date, '%d-%m-%Y');
+----------------------------------------------+
| to_char(Utf8("2023-03-01"),Utf8("%d-%m-%Y")) |
+----------------------------------------------+
| 01-03-2023                                   |
+----------------------------------------------+
```

#### Aliases

- date\_format

### `to_date`

Converts a value to a date (`YYYY-MM-DD`). Supports strings, integer and double 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`.

```sql
to_date('2017-05-31', '%Y-%m-%d')
```

#### Arguments

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

#### Example

```sql
> select to_date('2023-01-31');
+-------------------------------+
| to_date(Utf8("2023-01-31")) |
+-------------------------------+
| 2023-01-31                    |
+-------------------------------+
> select to_date('2023/01/31', '%Y-%m-%d', '%Y/%m/%d');
+---------------------------------------------------------------------+
| to_date(Utf8("2023/01/31"),Utf8("%Y-%m-%d"),Utf8("%Y/%m/%d")) |
+---------------------------------------------------------------------+
| 2023-01-31                                                          |
+---------------------------------------------------------------------+
```

### `to_local_time`

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.

```sql
to_local_time(expression)
```

#### Arguments

- **expression**: Time expression to operate on. Can be a constant, column, or function.

#### Example

```sql
> SELECT to_local_time('2024-04-01T00:00:20Z'::timestamp);
+---------------------------------------------+
| to_local_time(Utf8("2024-04-01T00:00:20Z")) |
+---------------------------------------------+
| 2024-04-01T00:00:20                         |
+---------------------------------------------+

> SELECT to_local_time('2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels');
+---------------------------------------------+
| to_local_time(Utf8("2024-04-01T00:00:20Z")) |
+---------------------------------------------+
| 2024-04-01T00:00:20                         |
+---------------------------------------------+

> SELECT
  time,
  arrow_typeof(time) as type,
  to_local_time(time) as to_local_time,
  arrow_typeof(to_local_time(time)) as to_local_time_type
FROM (
  SELECT '2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels' AS time
);
+---------------------------+------------------------------------------------+---------------------+-----------------------------+
| time                      | type                                           | to_local_time       | to_local_time_type          |
+---------------------------+------------------------------------------------+---------------------+-----------------------------+
| 2024-04-01T00:00:20+02:00 | Timestamp(Nanosecond, Some("Europe/Brussels")) | 2024-04-01T00:00:20 | Timestamp(Nanosecond, None) |
+---------------------------+------------------------------------------------+---------------------+-----------------------------+

# combine `to_local_time()` with `date_bin()` to bin on boundaries in the timezone rather
# than UTC boundaries

> SELECT date_bin(interval '1 day', to_local_time('2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels')) AS date_bin;
+---------------------+
| date_bin            |
+---------------------+
| 2024-04-01T00:00:00 |
+---------------------+

> SELECT date_bin(interval '1 day', to_local_time('2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels')) AT TIME ZONE 'Europe/Brussels' AS date_bin_with_timezone;
+---------------------------+
| date_bin_with_timezone    |
+---------------------------+
| 2024-04-01T00:00:00+02:00 |
+---------------------------+
```

### `to_timestamp`

Converts a value to a timestamp (`YYYY-MM-DDT00:00:00Z`). 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] are provided. Integers, unsigned integers, and doubles are interpreted as seconds since the unix epoch (`1970-01-01T00:00:00Z`). Returns the corresponding timestamp.

Note: `to_timestamp` returns `Timestamp(Nanosecond)`. 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.

```sql
to_timestamp(expression[, ..., format_n])
```

#### Arguments

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

#### Example

```sql
> select to_timestamp('2023-01-31T09:26:56.123456789-05:00');
+-----------------------------------------------------------+
| to_timestamp(Utf8("2023-01-31T09:26:56.123456789-05:00")) |
+-----------------------------------------------------------+
| 2023-01-31T14:26:56.123456789                             |
+-----------------------------------------------------------+
> select to_timestamp('03:59:00.123456789 05-17-2023', '%c', '%+', '%H:%M:%S%.f %m-%d-%Y');
+--------------------------------------------------------------------------------------------------------+
| to_timestamp(Utf8("03:59:00.123456789 05-17-2023"),Utf8("%c"),Utf8("%+"),Utf8("%H:%M:%S%.f %m-%d-%Y")) |
+--------------------------------------------------------------------------------------------------------+
| 2023-05-17T03:59:00.123456789                                                                          |
+--------------------------------------------------------------------------------------------------------+
```

### `to_timestamp_micros`

Converts a value to a timestamp (`YYYY-MM-DDT00:00:00.000000Z`). Supports strings, integer, and unsigned integer types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00') if no [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html)s are provided. Integers and unsigned integers are interpreted as microseconds since the unix epoch (`1970-01-01T00:00:00Z`) Returns the corresponding timestamp.

```sql
to_timestamp_micros(expression[, ..., format_n])
```

#### Arguments

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

#### Example

```sql
> select to_timestamp_micros('2023-01-31T09:26:56.123456789-05:00');
+------------------------------------------------------------------+
| to_timestamp_micros(Utf8("2023-01-31T09:26:56.123456789-05:00")) |
+------------------------------------------------------------------+
| 2023-01-31T14:26:56.123456                                       |
+------------------------------------------------------------------+
> select to_timestamp_micros('03:59:00.123456789 05-17-2023', '%c', '%+', '%H:%M:%S%.f %m-%d-%Y');
+---------------------------------------------------------------------------------------------------------------+
| to_timestamp_micros(Utf8("03:59:00.123456789 05-17-2023"),Utf8("%c"),Utf8("%+"),Utf8("%H:%M:%S%.f %m-%d-%Y")) |
+---------------------------------------------------------------------------------------------------------------+
| 2023-05-17T03:59:00.123456                                                                                    |
+---------------------------------------------------------------------------------------------------------------+
```

### `to_timestamp_millis`

Converts a value to a timestamp (`YYYY-MM-DDT00:00:00.000Z`). Supports strings, integer, and unsigned integer 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 and unsigned integers are interpreted as milliseconds since the unix epoch (`1970-01-01T00:00:00Z`). Returns the corresponding timestamp.

```sql
to_timestamp_millis(expression[, ..., format_n])
```

#### Arguments

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

#### Example

```sql
> select to_timestamp_millis('2023-01-31T09:26:56.123456789-05:00');
+------------------------------------------------------------------+
| to_timestamp_millis(Utf8("2023-01-31T09:26:56.123456789-05:00")) |
+------------------------------------------------------------------+
| 2023-01-31T14:26:56.123                                          |
+------------------------------------------------------------------+
> select to_timestamp_millis('03:59:00.123456789 05-17-2023', '%c', '%+', '%H:%M:%S%.f %m-%d-%Y');
+---------------------------------------------------------------------------------------------------------------+
| to_timestamp_millis(Utf8("03:59:00.123456789 05-17-2023"),Utf8("%c"),Utf8("%+"),Utf8("%H:%M:%S%.f %m-%d-%Y")) |
+---------------------------------------------------------------------------------------------------------------+
| 2023-05-17T03:59:00.123                                                                                       |
+---------------------------------------------------------------------------------------------------------------+
```

### `to_timestamp_nanos`

Converts a value to a timestamp (`YYYY-MM-DDT00:00:00.000000000Z`). Supports strings, integer, and unsigned integer types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00') if no [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html)s are provided. Integers and unsigned integers are interpreted as nanoseconds since the unix epoch (`1970-01-01T00:00:00Z`). Returns the corresponding timestamp.

```sql
to_timestamp_nanos(expression[, ..., format_n])
```

#### Arguments

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

#### Example

```sql
> select to_timestamp_nanos('2023-01-31T09:26:56.123456789-05:00');
+-----------------------------------------------------------------+
| to_timestamp_nanos(Utf8("2023-01-31T09:26:56.123456789-05:00")) |
+-----------------------------------------------------------------+
| 2023-01-31T14:26:56.123456789                                   |
+-----------------------------------------------------------------+
> select to_timestamp_nanos('03:59:00.123456789 05-17-2023', '%c', '%+', '%H:%M:%S%.f %m-%d-%Y');
+--------------------------------------------------------------------------------------------------------------+
| to_timestamp_nanos(Utf8("03:59:00.123456789 05-17-2023"),Utf8("%c"),Utf8("%+"),Utf8("%H:%M:%S%.f %m-%d-%Y")) |
+--------------------------------------------------------------------------------------------------------------+
| 2023-05-17T03:59:00.123456789                                                                                |
+---------------------------------------------------------------------------------------------------------------+
```

### `to_timestamp_seconds`

Converts a value to a timestamp (`YYYY-MM-DDT00:00:00.000Z`). Supports strings, integer, and unsigned integer types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00') if no [Chrono format](https://docs.rs/chrono/latest/chrono/format/strftime/index.html)s are provided. Integers and unsigned integers are interpreted as seconds since the unix epoch (`1970-01-01T00:00:00Z`). Returns the corresponding timestamp.

```sql
to_timestamp_seconds(expression[, ..., format_n])
```

#### Arguments

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

#### Example

```sql
> select to_timestamp_seconds('2023-01-31T09:26:56.123456789-05:00');
+-------------------------------------------------------------------+
| to_timestamp_seconds(Utf8("2023-01-31T09:26:56.123456789-05:00")) |
+-------------------------------------------------------------------+
| 2023-01-31T14:26:56                                               |
+-------------------------------------------------------------------+
> select to_timestamp_seconds('03:59:00.123456789 05-17-2023', '%c', '%+', '%H:%M:%S%.f %m-%d-%Y');
+----------------------------------------------------------------------------------------------------------------+
| to_timestamp_seconds(Utf8("03:59:00.123456789 05-17-2023"),Utf8("%c"),Utf8("%+"),Utf8("%H:%M:%S%.f %m-%d-%Y")) |
+----------------------------------------------------------------------------------------------------------------+
| 2023-05-17T03:59:00                                                                                            |
+----------------------------------------------------------------------------------------------------------------+
```

### `to_unixtime`

Converts a value to seconds since the unix epoch (`1970-01-01T00:00:00Z`). Supports strings, dates, timestamps 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.

```sql
to_unixtime(expression[, ..., format_n])
```

#### Arguments

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

#### Example

```sql
> select to_unixtime('2020-09-08T12:00:00+00:00');
+------------------------------------------------+
| to_unixtime(Utf8("2020-09-08T12:00:00+00:00")) |
+------------------------------------------------+
| 1599566400                                     |
+------------------------------------------------+
> select to_unixtime('01-14-2023 01:01:30+05:30', '%q', '%d-%m-%Y %H/%M/%S', '%+', '%m-%d-%Y %H:%M:%S%#z');
+-----------------------------------------------------------------------------------------------------------------------------+
| to_unixtime(Utf8("01-14-2023 01:01:30+05:30"),Utf8("%q"),Utf8("%d-%m-%Y %H/%M/%S"),Utf8("%+"),Utf8("%m-%d-%Y %H:%M:%S%#z")) |
+-----------------------------------------------------------------------------------------------------------------------------+
| 1673638290                                                                                                                  |
+-----------------------------------------------------------------------------------------------------------------------------+
```

### `today`

_Alias of [current\_date](https://spice.ai/docs/sql/scalar-functions#current_date)._

## Array Functions

Array functions in Spice.ai SQL help construct, transform, and query array data types. These functions operate on array expressions, which can be constants, columns, or results of other functions. The implementation closely follows the PostgreSQL dialect. The following array functions are supported:

- [array](https://spice.ai/docs/sql/scalar-functions#array)
- [array\_add](https://spice.ai/docs/sql/scalar-functions#array_add)
- [array\_any\_value](https://spice.ai/docs/sql/scalar-functions#array_any_value)
- [array\_append](https://spice.ai/docs/sql/scalar-functions#array_append)
- [array\_avg](https://spice.ai/docs/sql/scalar-functions#array_avg)
- [array\_cat](https://spice.ai/docs/sql/scalar-functions#array_cat)
- [array\_concat](https://spice.ai/docs/sql/scalar-functions#array_concat)
- [array\_contains](https://spice.ai/docs/sql/scalar-functions#array_contains)
- [array\_dims](https://spice.ai/docs/sql/scalar-functions#array_dims)
- [array\_distance](https://spice.ai/docs/sql/scalar-functions#array_distance)
- [array\_distinct](https://spice.ai/docs/sql/scalar-functions#array_distinct)
- [array\_element](https://spice.ai/docs/sql/scalar-functions#array_element)
- [array\_except](https://spice.ai/docs/sql/scalar-functions#array_except)
- [array\_has](https://spice.ai/docs/sql/scalar-functions#array_has)
- [array\_has\_all](https://spice.ai/docs/sql/scalar-functions#array_has_all)
- [array\_has\_any](https://spice.ai/docs/sql/scalar-functions#array_has_any)
- [array\_intersect](https://spice.ai/docs/sql/scalar-functions#array_intersect)
- [array\_length](https://spice.ai/docs/sql/scalar-functions#array_length)
- [array\_max](https://spice.ai/docs/sql/scalar-functions#array_max)
- [array\_min](https://spice.ai/docs/sql/scalar-functions#array_min)
- [array\_ndims](https://spice.ai/docs/sql/scalar-functions#array_ndims)
- [array\_pop\_back](https://spice.ai/docs/sql/scalar-functions#array_pop_back)
- [array\_pop\_front](https://spice.ai/docs/sql/scalar-functions#array_pop_front)
- [array\_position](https://spice.ai/docs/sql/scalar-functions#array_position)
- [array\_positions](https://spice.ai/docs/sql/scalar-functions#array_positions)
- [array\_prepend](https://spice.ai/docs/sql/scalar-functions#array_prepend)
- [array\_product](https://spice.ai/docs/sql/scalar-functions#array_product)
- [array\_remove](https://spice.ai/docs/sql/scalar-functions#array_remove)
- [array\_remove\_n](https://spice.ai/docs/sql/scalar-functions#array_remove_n)
- [array\_remove\_all](https://spice.ai/docs/sql/scalar-functions#array_remove_all)
- [array\_repeat](https://spice.ai/docs/sql/scalar-functions#array_repeat)
- [array\_replace](https://spice.ai/docs/sql/scalar-functions#array_replace)
- [array\_replace\_n](https://spice.ai/docs/sql/scalar-functions#array_replace_n)
- [array\_replace\_all](https://spice.ai/docs/sql/scalar-functions#array_replace_all)
- [array\_resize](https://spice.ai/docs/sql/scalar-functions#array_resize)
- [array\_reverse](https://spice.ai/docs/sql/scalar-functions#array_reverse)
- [array\_scale](https://spice.ai/docs/sql/scalar-functions#array_scale)
- [array\_slice](https://spice.ai/docs/sql/scalar-functions#array_slice)
- [array\_sort](https://spice.ai/docs/sql/scalar-functions#array_sort)
- [array\_subtract](https://spice.ai/docs/sql/scalar-functions#array_subtract)
- [array\_sum](https://spice.ai/docs/sql/scalar-functions#array_sum)
- [array\_to\_string](https://spice.ai/docs/sql/scalar-functions#array_to_string)
- [array\_union](https://spice.ai/docs/sql/scalar-functions#array_union)
- [arrays\_zip](https://spice.ai/docs/sql/scalar-functions#arrays_zip)
- [cardinality](https://spice.ai/docs/sql/scalar-functions#cardinality)
- [empty](https://spice.ai/docs/sql/scalar-functions#empty)
- [flatten](https://spice.ai/docs/sql/scalar-functions#flatten)
- [make\_array](https://spice.ai/docs/sql/scalar-functions#make_array)
- [range](https://spice.ai/docs/sql/scalar-functions#range)
- [generate\_series](https://spice.ai/docs/sql/scalar-functions#generate_series)
- [string\_to\_array](https://spice.ai/docs/sql/scalar-functions#string_to_array)

### `array`

Constructs an array from the provided expressions using Spark-compatible semantics. Inputs are evaluated left to right, cast to a common element type, and collected into a single Arrow list value without removing duplicates or nulls.

```sql
array(expression[, ..., expression_n])
```

#### Arguments

- **expression**: Value to include in the array. Expressions must be implicitly castable to a shared element type.
- **expression\_n**: Additional expressions to append to the array.

#### Example

```sql
> select array(1, 2, 3);
+-----------------------------------+
| array(Int64(1),Int64(2),Int64(3)) |
+-----------------------------------+
| [1, 2, 3]                         |
+-----------------------------------+
```

Reference: [Spark SQL `array`](https://spark.apache.org/docs/latest/api/sql/index.html#array).

### `array_add`

Returns the element-wise sum of two numeric arrays of equal length, computed as `array1[i] + array2[i]`. The result is an array of `Float64` values. If either element at a position is `NULL`, the result element at that position is `NULL`. Returns `NULL` if either array is `NULL`. Returns an error if the two arrays in a row have different lengths.

```sql
array_add(array1, array2)
```

#### Arguments

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

#### Example

```sql
> select array_add([1.0, 2.0, 3.0], [10.0, 20.0, 30.0]);
+---------------------------------------------------------------------------------------------------------+
| array_add(make_array(Float64(1),Float64(2),Float64(3)),make_array(Float64(10),Float64(20),Float64(30))) |
+---------------------------------------------------------------------------------------------------------+
| [11.0, 22.0, 33.0]                                                                                      |
+---------------------------------------------------------------------------------------------------------+
```

#### Aliases

- list\_add

### `array_any_value`

Returns the first non-null element in the array. If all elements are null, returns null.

```sql
array_any_value(array)
```

#### Arguments

- **array**: Array expression. Can be a constant, column, or function, and any combination of array operators.

#### Example

```sql
> select array_any_value([NULL, 1, 2, 3]);
+-------------------------------+
| array_any_value(List([NULL,1,2,3])) |
+-------------------------------------+
| 1                                   |
+-------------------------------------+
```

#### Aliases

- list\_any\_value

### `array_append`

Appends an element to the end of an array and returns the new array.

```sql
array_append(array, element)
```

#### Arguments

- **array**: Array expression. Can be a constant, column, or function, and any combination of array operators.
- **element**: Element to append to the array.

#### Example

```sql
> select array_append([1, 2, 3], 4);
+--------------------------------------+
| array_append(List([1,2,3]),Int64(4)) |
+--------------------------------------+
| [1, 2, 3, 4]                         |
+--------------------------------------+
```

#### Aliases

- list\_append
- array\_push\_back
- list\_push\_back

### `array_avg`

Returns the arithmetic mean of the elements of a numeric array as a `Float64` value. `NULL` elements are skipped and are not counted. Returns `NULL` if the array is `NULL`, is empty, or contains only `NULL` elements.

```sql
array_avg(array)
```

#### Arguments

- **array**: Array expression. Can be a constant, column, or function, and any combination of array operators.

#### Example

```sql
> select array_avg([1.0, NULL, 3.0]);
+---------------------------------------------------+
| array_avg(make_array(Float64(1),NULL,Float64(3))) |
+---------------------------------------------------+
| 2.0                                               |
+---------------------------------------------------+
```

#### Aliases

- list\_avg

### `array_cat`

Alias of [`array_concat`](https://spice.ai/docs/sql/scalar-functions#array_concat).

### `array_concat`

Concatenates two or more arrays into a single array.

```sql
array_concat(array[, ..., array_n])
```

#### Arguments

- **array**: Array expression. Can be a constant, column, or function, and any combination of array operators.
- **array\_n**: Additional array expressions to concatenate.

#### Example

```sql
> select array_concat([1, 2], [3, 4], [5, 6]);
+---------------------------------------------------+
| array_concat(List([1,2]),List([3,4]),List([5,6])) |
+---------------------------------------------------+
| [1, 2, 3, 4, 5, 6]                                |
+---------------------------------------------------+
```

#### Aliases

- array\_cat
- list\_concat
- list\_cat

### `array_contains`

Returns true if the array contains the specified element.

```sql
array_contains(array, element)
```

#### Arguments

- **array**: Array expression. Can be a constant, column, or function, and any combination of array operators.
- **element**: Element to search for in the array.

#### Example

```sql
> select array_contains([1, 2, 3], 2);
+----------------------------------------+
| array_contains(List([1,2,3]),Int64(2)) |
+----------------------------------------+
| true                                   |
+----------------------------------------+
```

**Note**: For array-to-array containment operations, use the [`@>` operator](https://spice.ai/docs/sql/operators#op_arr_contains).

### `array_dims`

Returns an array of the array's dimensions. For a 2D array, returns the number of rows and columns.

```sql
array_dims(array)
```

#### Arguments

- **array**: Array expression. Can be a constant, column, or function, and any combination of array operators.

#### Example

```sql
> select array_dims([[1, 2, 3], [4, 5, 6]]);
+---------------------------------+
| array_dims(List([1,2,3,4,5,6])) |
+---------------------------------+
| [2, 3]                          |
+---------------------------------+
```

#### Aliases

- list\_dims

### `array_distance`

Returns the Euclidean distance between two input arrays of equal length.

```sql
array_distance(array1, array2)
```

#### Arguments

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

#### Example

```sql
> select array_distance([1, 2], [1, 4]);
+------------------------------------+
| array_distance(List([1,2], [1,4])) |
+------------------------------------+
| 2.0                                |
+------------------------------------+
```

#### Aliases

- list\_distance

### `array_distinct`

Returns a new array with duplicate elements removed, preserving the order of first occurrence.

```sql
array_distinct(array)
```

#### Arguments

- **array**: Array expression. Can be a constant, column, or function, and any combination of array operators.

#### Example

```sql
> select array_distinct([1, 3, 2, 3, 1, 2, 4]);
+---------------------------------+
| array_distinct(List([1,2,3,4])) |
+---------------------------------+
| [1, 3, 2, 4]                    |
+---------------------------------+
```

#### Aliases

- list\_distinct

### `array_element`

Extracts the element at the specified index from the array. Indexing is 1-based.

```sql
array_element(array, index)
```

#### Arguments

- **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 (1-based).

#### Example

```sql
> select array_element([1, 2, 3, 4], 3);
+-----------------------------------------+
| array_element(List([1,2,3,4]),Int64(3)) |
+-----------------------------------------+
| 3                                       |
+-----------------------------------------+
```

#### Aliases

- array\_extract
- list\_element
- list\_extract

### `array_except`

Returns an array containing elements in `array1` that are not in `array2`, preserving first-occurrence order and without duplicates.

```sql
array_except(array1, array2)
```

Alias: `list_except`.

### `array_has`

Returns `true` if the array contains the specified element.

```sql
array_has(array, element)
```

Aliases: `array_contains`, `list_has`.

### `array_has_all`

Returns `true` if every element of `sub_array` is present in `array`.

```sql
array_has_all(array, sub_array)
```

Alias: `list_has_all`.

### `array_has_any`

Returns `true` if `array` and `sub_array` share at least one element.

```sql
array_has_any(array, sub_array)
```

Aliases: `list_has_any`, `arrays_overlap`.

### `array_intersect`

Returns an array of elements present in both input arrays, deduplicated.

```sql
array_intersect(array1, array2)
```

Alias: `list_intersect`.

### `array_length`

Returns the length of the array at the given (optional) dimension. Dimension defaults to 1.

```sql
array_length(array[, dimension])
```

Alias: `list_length`.

### `array_max`

Returns the maximum element of the array, ignoring `NULL`s.

```sql
array_max(array)
```

Alias: `list_max`.

### `array_min`

Returns the minimum element of the array, ignoring `NULL`s.

```sql
array_min(array)
```

Alias: `list_min`.

### `array_ndims`

Returns the number of dimensions of the array.

```sql
array_ndims(array)
```

Alias: `list_ndims`.

### `array_pop_back`

Returns the array with the last element removed.

```sql
array_pop_back(array)
```

Alias: `list_pop_back`.

### `array_pop_front`

Returns the array with the first element removed.

```sql
array_pop_front(array)
```

Alias: `list_pop_front`.

### `array_position`

Returns the 1-based position of the first occurrence of `element` in `array`, or `NULL` if not found. An optional `from_index` starts the search at a later position.

```sql
array_position(array, element[, from_index])
```

Aliases: `list_position`, `array_indexof`, `list_indexof`.

### `array_positions`

Returns a 1-based array of all positions where `element` occurs in `array`.

```sql
array_positions(array, element)
```

Alias: `list_positions`.

### `array_prepend`

Prepends an element to the beginning of an array.

```sql
array_prepend(element, array)
```

Aliases: `list_prepend`, `array_push_front`, `list_push_front`.

### `array_product`

Returns the product of the elements of a numeric array as a `Float64` value. `NULL` elements are skipped. Returns `NULL` if the array is `NULL`, is empty, or contains only `NULL` elements.

```sql
array_product(array)
```

#### Arguments

- **array**: Array expression. Can be a constant, column, or function, and any combination of array operators.

#### Example

```sql
> select array_product([2.0, 3.0, 4.0]);
+-------------------------------------------------------------+
| array_product(make_array(Float64(2),Float64(3),Float64(4))) |
+-------------------------------------------------------------+
| 24.0                                                        |
+-------------------------------------------------------------+
```

#### Aliases

- list\_product

### `array_remove`

Returns the array with the first occurrence of `element` removed.

```sql
array_remove(array, element)
```

Alias: `list_remove`.

### `array_remove_n`

Returns the array with the first `max` occurrences of `element` removed.

```sql
array_remove_n(array, element, max)
```

Alias: `list_remove_n`.

### `array_remove_all`

Returns the array with all occurrences of `element` removed.

```sql
array_remove_all(array, element)
```

Alias: `list_remove_all`.

### `array_repeat`

Returns an array containing `element` repeated `count` times.

```sql
array_repeat(element, count)
```

Alias: `list_repeat`.

### `array_replace`

Replaces the first occurrence of `from` with `to` in `array`.

```sql
array_replace(array, from, to)
```

Alias: `list_replace`.

### `array_replace_n`

Replaces the first `max` occurrences of `from` with `to` in `array`.

```sql
array_replace_n(array, from, to, max)
```

Alias: `list_replace_n`.

### `array_replace_all`

Replaces every occurrence of `from` with `to` in `array`.

```sql
array_replace_all(array, from, to)
```

Alias: `list_replace_all`.

### `array_resize`

Resizes `array` to the given length, padding with `value` (or `NULL` if omitted) when growing.

```sql
array_resize(array, size[, value])
```

Alias: `list_resize`.

### `array_reverse`

Returns the array with elements in reverse order.

```sql
array_reverse(array)
```

Alias: `list_reverse`.

### `array_scale`

Multiplies each element of a numeric array by a numeric scalar, computed as `array[i] * scalar`. The result is an array of `Float64` values. A `NULL` element stays `NULL`. Returns `NULL` if the array or the scalar is `NULL`, and an empty array for an empty array.

```sql
array_scale(array, scalar)
```

#### Arguments

- **array**: Array expression. Can be a constant, column, or function, and any combination of array operators.
- **scalar**: Numeric value to multiply each element by. Can be a constant or a column.

#### Example

```sql
> select array_scale([1.0, 2.0, 3.0], 2.0);
+----------------------------------------------------------------------+
| array_scale(make_array(Float64(1),Float64(2),Float64(3)),Float64(2)) |
+----------------------------------------------------------------------+
| [2.0, 4.0, 6.0]                                                      |
+----------------------------------------------------------------------+
```

#### Aliases

- list\_scale

### `array_slice`

Returns a slice of the array from `begin` to `end` (1-based, inclusive). Negative indices count from the end.

```sql
array_slice(array, begin, end[, stride])
```

Alias: `list_slice`.

### `array_sort`

Returns `array` sorted in ascending order (default). Optional arguments control sort direction (`ASC`/`DESC`) and null placement (`NULLS FIRST`/`NULLS LAST`).

```sql
array_sort(array[, desc[, nulls_first]])
```

Alias: `list_sort`.

### `array_subtract`

Returns the element-wise difference of two numeric arrays of equal length, computed as `array1[i] - array2[i]`. The result is an array of `Float64` values. If either element at a position is `NULL`, the result element at that position is `NULL`. Returns `NULL` if either array is `NULL`. Returns an error if the two arrays in a row have different lengths.

```sql
array_subtract(array1, array2)
```

#### Arguments

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

#### Example

```sql
> select array_subtract([10.0, 20.0, 30.0], [1.0, 2.0, 3.0]);
+--------------------------------------------------------------------------------------------------------------+
| array_subtract(make_array(Float64(10),Float64(20),Float64(30)),make_array(Float64(1),Float64(2),Float64(3))) |
+--------------------------------------------------------------------------------------------------------------+
| [9.0, 18.0, 27.0]                                                                                            |
+--------------------------------------------------------------------------------------------------------------+
```

#### Aliases

- list\_subtract

### `array_sum`

Returns the sum of the elements of a numeric array as a `Float64` value. `NULL` elements are skipped. Returns `NULL` if the array is `NULL`, is empty, or contains only `NULL` elements.

```sql
array_sum(array)
```

#### Arguments

- **array**: Array expression. Can be a constant, column, or function, and any combination of array operators.

#### Example

```sql
> select array_sum([1.0, NULL, 3.0]);
+---------------------------------------------------+
| array_sum(make_array(Float64(1),NULL,Float64(3))) |
+---------------------------------------------------+
| 4.0                                               |
+---------------------------------------------------+
```

#### Aliases

- list\_sum

### `array_to_string`

Concatenates array elements into a single string using the given delimiter. Optional `null_string` replaces `NULL` elements.

```sql
array_to_string(array, delimiter[, null_string])
```

Aliases: `list_to_string`, `array_join`, `list_join`.

### `array_union`

Returns the set-union of two arrays, deduplicated.

```sql
array_union(array1, array2)
```

Alias: `list_union`.

### `arrays_zip`

Merges the given arrays element-wise into an array of structs. Shorter arrays are padded with `NULL`s.

```sql
arrays_zip(array1[, array2, ...])
```

Alias: `list_zip`.

### `cardinality`

Returns the total number of elements in an array (including nested elements) or the number of entries in a map.

```sql
cardinality(array_or_map)
```

### `empty`

Returns `true` if the array has length 0 (or is `NULL`).

```sql
empty(array)
```

Aliases: `array_empty`, `list_empty`.

### `flatten`

Flattens a nested array into a single-level array.

```sql
flatten(array)
```

### `make_array`

Constructs an array (Arrow list) from the given expressions. SQL `[expr1, expr2, ...]` literal syntax compiles to this function.

```sql
make_array(expression1[, ..., expression_n])
```

Alias: `make_list`.

### `range`

Generates a numeric or date range as an array, half-open on the upper bound. When the step is omitted, the default is `1`.

```sql
range(start, stop[, step])
range(stop)
```

For dates, `step` is an interval literal, e.g. `interval '1 day'`. Use [`generate_series`](https://spice.ai/docs/sql/scalar-functions#generate_series) for the inclusive-upper-bound variant.

### `generate_series`

Like [`range`](https://spice.ai/docs/sql/scalar-functions#range), but the upper bound is inclusive.

```sql
generate_series(start, stop[, step])
```

### `string_to_array`

Splits a string into an array of substrings using the given delimiter. An optional `null_string` turns matching substrings into `NULL`s.

```sql
string_to_array(str, delimiter[, null_string])
```

Alias: `string_to_list`.

---

## Struct Functions

Struct functions help construct and access structured data types (Arrow structs). These are useful for working with nested or composite data.

- [struct](https://spice.ai/docs/sql/scalar-functions#struct)
- [named\_struct](https://spice.ai/docs/sql/scalar-functions#named_struct)
- [get\_field](https://spice.ai/docs/sql/scalar-functions#get_field)

### `struct`

Constructs an anonymous Arrow struct from the given values. Field names default to `c0`, `c1`, ... in the order provided.

```sql
struct(expression1[, ..., expression_n])
```

#### Example

```sql
> SELECT struct(1, 'spice', true);
+-----------------------------------------------------+
| struct(Int64(1),Utf8("spice"),Boolean(true))        |
+-----------------------------------------------------+
| {c0: 1, c1: spice, c2: true}                        |
+-----------------------------------------------------+
```

### `named_struct`

Constructs an Arrow struct from alternating field-name / field-value pairs.

```sql
named_struct(name1, expression1[, name2, expression2, ...])
```

#### Example

```sql
> SELECT named_struct('id', 1, 'label', 'spice');
+--------------------------------------------------------+
| named_struct(Utf8("id"),Int64(1),Utf8("label"),Utf8("spice")) |
+--------------------------------------------------------+
| {id: 1, label: spice}                                  |
+--------------------------------------------------------+
```

### `get_field`

Extracts a field by name from a struct or map. `struct.field` and `struct['field']` sugar invoke this function.

```sql
get_field(expression, field_name)
```

## Map Functions

Map functions help construct and query key-value data structures. These are useful for semi-structured or JSON-like data.

- [map](https://spice.ai/docs/sql/scalar-functions#map)
- [map\_keys](https://spice.ai/docs/sql/scalar-functions#map_keys)
- [map\_values](https://spice.ai/docs/sql/scalar-functions#map_values)
- [map\_entries](https://spice.ai/docs/sql/scalar-functions#map_entries)
- [map\_extract](https://spice.ai/docs/sql/scalar-functions#map_extract)

### `map`

Constructs an Arrow map from alternating key/value arguments, or from two arrays (one of keys, one of values).

```sql
map(key1, value1[, key2, value2, ...])
map(keys_array, values_array)
```

#### Example

```sql
> SELECT map('a', 1, 'b', 2);
+-------------------------------------------------------+
| map(Utf8("a"),Int64(1),Utf8("b"),Int64(2))            |
+-------------------------------------------------------+
| {a: 1, b: 2}                                          |
+-------------------------------------------------------+
```

### `map_keys`

Returns the keys of a map as an array.

```sql
map_keys(map)
```

### `map_values`

Returns the values of a map as an array.

```sql
map_values(map)
```

### `map_entries`

Returns the entries of a map as an array of structs `[{key, value}, ...]`.

```sql
map_entries(map)
```

### `map_extract`

Looks up a key in a map and returns the associated value, or `NULL` if the key is absent.

```sql
map_extract(map, key)
```

Alias: `element_at`.

## Hashing Functions

Hashing functions compute cryptographic hashes and checksums for data integrity, fingerprinting, and security applications. Binary digest output is returned as a `Binary` (bytes) array; use `encode(..., 'hex')` to render as hex.

- [digest](https://spice.ai/docs/sql/scalar-functions#digest)
- [md5](https://spice.ai/docs/sql/scalar-functions#md5)
- [sha224](https://spice.ai/docs/sql/scalar-functions#sha224)
- [sha256](https://spice.ai/docs/sql/scalar-functions#sha256)
- [sha384](https://spice.ai/docs/sql/scalar-functions#sha384)
- [sha512](https://spice.ai/docs/sql/scalar-functions#sha512)

### `digest`

Computes the digest of the input using the named hash algorithm. Supported algorithms: `'md5'`, `'sha224'`, `'sha256'`, `'sha384'`, `'sha512'`, `'blake2s'`, `'blake2b'`, `'blake3'`.

```sql
digest(expression, algorithm)
```

#### Example

```sql
> SELECT encode(digest('spice.ai', 'sha256'), 'hex');
```

### `md5`

Computes the MD5 128-bit hash of a string and returns the result as a lowercase hex string.

```sql
md5(expression)
```

### `sha224`

Computes the SHA-224 hash and returns a binary digest.

```sql
sha224(expression)
```

### `sha256`

Computes the SHA-256 hash and returns a binary digest.

```sql
sha256(expression)
```

### `sha384`

Computes the SHA-384 hash and returns a binary digest.

```sql
sha384(expression)
```

### `sha512`

Computes the SHA-512 hash and returns a binary digest.

```sql
sha512(expression)
```

## Encoding Functions

Binary encoding utilities for converting between binary data and text representations.

- [encode](https://spice.ai/docs/sql/scalar-functions#encode)
- [decode](https://spice.ai/docs/sql/scalar-functions#decode)

### `encode`

Encodes a string or binary value using the specified encoding. Supported encodings: `'hex'`, `'base64'`.

```sql
encode(expression, encoding)
```

#### Example

```sql
> SELECT encode('spice', 'base64');
+--------------------------------------+
| encode(Utf8("spice"),Utf8("base64")) |
+--------------------------------------+
| c3BpY2U=                             |
+--------------------------------------+
```

### `decode`

Decodes text back to binary using the specified encoding. Supported encodings: `'hex'`, `'base64'`.

```sql
decode(expression, encoding)
```

## Union Functions

Union functions help work with union (variant) data types.

- [union\_extract](https://spice.ai/docs/sql/scalar-functions#union_extract)
- [union\_tag](https://spice.ai/docs/sql/scalar-functions#union_tag)

### `union_extract`

Extracts the value of a named member from a union, returning `NULL` if the union's active member doesn't match.

```sql
union_extract(expression, field_name)
```

### `union_tag`

Returns the name of the active member of a union value as a string.

```sql
union_tag(expression)
```

## Metadata Functions

PostgreSQL-compatible functions for reading table and column comments from registered datasets. Comments originate from the dataset's source (for connectors that surface `COMMENT ON TABLE` / `COMMENT ON COLUMN` metadata, such as PostgreSQL, MySQL, Snowflake, and Databricks) or from `description` metadata attached to the dataset schema.

- [obj\_description](https://spice.ai/docs/sql/scalar-functions#obj_description)
- [col\_description](https://spice.ai/docs/sql/scalar-functions#col_description)

### `obj_description`

Returns the comment attached to a registered table, or `NULL` if the table has no comment.

```sql
obj_description(table_identifier)
obj_description(table_identifier, catalog_name)
obj_description(schema_name, table_name)
obj_description(catalog_name, schema_name, table_name)
```

#### Arguments

- **table\_identifier**: Either a string holding a possibly-qualified table name (`'table'`, `'schema.table'`, or `'catalog.schema.table'`) or an integer table OID. Unqualified names are resolved against the session's default catalog and schema.
- **catalog\_name**: When supplied as the second argument with `'pg_class'`, the call is treated as PostgreSQL-style `obj_description(oid, 'pg_class')`; any other value returns `NULL`.
- **schema\_name**, **table\_name**: Explicit schema and table parts. The three-argument form additionally takes the catalog as the first argument.

#### Example

```sql
> SELECT obj_description('public.taxi_trips');
+----------------------------------------+
| obj_description(Utf8("public.taxi_trips")) |
+----------------------------------------+
| NYC yellow-cab trip records             |
+----------------------------------------+
```

### `col_description`

Returns the comment attached to a column on a registered table, or `NULL` if no comment exists.

```sql
col_description(table_identifier, column)
col_description(catalog_name, schema_name, table_name, column)
```

#### Arguments

- **table\_identifier**: Possibly-qualified table name (string) or table OID (integer), resolved the same way as in [`obj_description`](https://spice.ai/docs/sql/scalar-functions#obj_description).
- **column**: Either a column name (string) or a 1-based ordinal position (integer).
- **catalog\_name**, **schema\_name**, **table\_name**: Explicit catalog, schema, and table parts when the four-argument form is used.

#### Example

```sql
> SELECT col_description('public.taxi_trips', 'fare_amount');
+---------------------------------------------------------+
| col_description(Utf8("public.taxi_trips"),Utf8("fare_amount")) |
+---------------------------------------------------------+
| Total fare in USD, excluding tip                        |
+---------------------------------------------------------+

> SELECT col_description('public.taxi_trips', 3);
+--------------------------------------------------+
| col_description(Utf8("public.taxi_trips"),Int64(3)) |
+--------------------------------------------------+
| Pickup datetime in source-local time             |
+--------------------------------------------------+
```

---

## Other Functions

Additional scalar functions include type casting, type inspection, and version reporting.

- [arrow\_cast](https://spice.ai/docs/sql/scalar-functions#arrow_cast)
- [arrow\_try\_cast](https://spice.ai/docs/sql/scalar-functions#arrow_try_cast)
- [arrow\_typeof](https://spice.ai/docs/sql/scalar-functions#arrow_typeof)
- [arrow\_metadata](https://spice.ai/docs/sql/scalar-functions#arrow_metadata)
- [version](https://spice.ai/docs/sql/scalar-functions#version)
- [ai](https://spice.ai/docs/sql/scalar-functions#ai-and-embed)
- [embed](https://spice.ai/docs/sql/scalar-functions#ai-and-embed)
- [bucket](https://spice.ai/docs/sql/scalar-functions#bucket)
- [truncate](https://spice.ai/docs/sql/scalar-functions#truncate)

### `arrow_cast`

Casts an expression to a specific Arrow data type. Use this function when you need precise control over the target Arrow type, such as specifying timestamp precision.

```sql
arrow_cast(expression, arrow_type)
```

#### Arguments

- **expression**: The value to cast.
- **arrow\_type**: A string specifying the target Arrow type (e.g., `'Int32'`, `'Utf8'`, `'Timestamp(Second, None)'`).

#### Example

```sql
> SELECT arrow_cast(now(), 'Timestamp(Second, None)') AS now_seconds;
+---------------------+
| now_seconds         |
+---------------------+
| 2024-01-15T10:30:45 |
+---------------------+

> SELECT arrow_cast('123', 'Int64') AS num;
+-----+
| num |
+-----+
| 123 |
+-----+
```

See [Data Types Reference](https://spiceai.org/docs/reference/datatypes) for supported Arrow types.

An `arrow_type` string nested more than 64 parentheses deep, such as a `List(...)` type wrapped in more than 64 levels of `List`, is rejected before the query is planned. The error contains `Query rejected: an 'arrow_cast' type string is nested more than 64 levels deep. Simplify the target type.` The same limit applies to `arrow_try_cast`.

### `arrow_try_cast`

Like [`arrow_cast`](https://spice.ai/docs/sql/scalar-functions#arrow_cast) but returns `NULL` instead of erroring when the cast fails.

```sql
arrow_try_cast(expression, arrow_type)
```

### `arrow_typeof`

Returns the Arrow data type of the given expression as a string.

```sql
arrow_typeof(expression)
```

#### Arguments

- **expression**: Any SQL expression.

#### Example

```sql
> SELECT arrow_typeof(1);
+------------------------+
| arrow_typeof(Int64(1)) |
+------------------------+
| Int64                  |
+------------------------+

> SELECT arrow_typeof(now());
+-------------------------------+
| arrow_typeof(now())           |
+-------------------------------+
| Timestamp(Nanosecond, None)   |
+-------------------------------+

> SELECT arrow_typeof(interval '1 month');
+------------------------------+
| arrow_typeof(...)            |
+------------------------------+
| Interval(MonthDayNano)       |
+------------------------------+
```

### `arrow_metadata`

Returns the Arrow schema metadata associated with an expression as a map of key/value strings. Useful for inspecting field-level metadata (units, comments, logical type hints) attached during ingest.

```sql
arrow_metadata(expression)
```

### `version`

Returns the underlying DataFusion runtime version string.

```sql
version()
```

### `ai` and `embed`

See [AI Functions](https://spice.ai/docs/sql/ai-functions) for `ai()` (LLM text generation), the [decision functions](https://spice.ai/docs/sql/ai-functions#decision-functions) `ai_if`, `ai_probability`, `ai_classify`, `ai_score`, and `ai_decide` (typed decisions), and `embed()` (vector embedding generation).

### `bucket`

Assigns a deterministic bucket identifier for a value by hashing the input and projecting it into a fixed number of buckets. Helpful for `partition_by` expressions and for co-locating related rows during acceleration refreshes.

```sql
bucket(num_buckets, value)
```

#### Arguments

- **num\_buckets**: Positive integer literal indicating how many buckets to distribute values across. Must be in the range `[1, 1_000_000]`. The literal's integer type (`Int8` … `Int64`, `UInt8` … `UInt64`) determines the return type.
- **value**: Expression to hash. Accepts strings, numbers, and other scalar types supported by the query engine.

#### Return Type

Returns an integer in the range `[0, num_buckets - 1]`, matching the integer type of `num_buckets`. The same input value always maps to the same bucket for a given `num_buckets` (the hash uses a fixed seed, so buckets are stable across processes and runtime restarts).

#### Example

```sql
-- Partition account IDs into 100 stable buckets
SELECT account_id, bucket(100, account_id) AS account_bucket
FROM accounts;
```

In `spicepod.yaml`, use the function directly inside `partition_by` to build file-based accelerations:

```yaml
datasets:
  - name: my_table
    acceleration:
      enabled: true
      engine: duckdb
      mode: file
      partition_by:
        - bucket(100, account_id)
```

### `truncate`

Iceberg-style truncate transform. For numeric values, rounds down to the nearest multiple of `width`. For strings and binary, returns the first `width` characters/bytes. Useful for partitioning by wide numeric ranges or string prefixes.

```sql
truncate(width, value)
```

#### Arguments

- **width**: Positive `Int64` literal that defines the bucket size or, for strings/binary, the number of leading units to retain. Maximum: `i64::MAX / 2`.
- **value**: Expression to truncate. Accepts:
  - Signed integers: `Int8`, `Int16`, `Int32`, `Int64`
  - Unsigned integers: `UInt8`, `UInt16`, `UInt32`, `UInt64`
  - Decimals: `Decimal128`, `Decimal256`
  - Strings: `Utf8`
  - Binary: `Binary`

#### Return Type

Returns the same type as `value`. For numbers, the result is the largest multiple of `width` that is less than or equal to `value`. For strings/binary, the result is the first `width` characters/bytes.

#### Example

```sql
-- Numeric: floor-bucket integers into ranges of 10
SELECT truncate(10, 101) AS truncated_id;  -- returns 100

-- Truncate event timestamps to the start of each hour (3600 seconds)
SELECT truncate(3600, extract(epoch FROM event_time)) AS hour_start
FROM events;

-- String: keep the first 2 characters (e.g., country prefix)
SELECT truncate(2, 'United Kingdom');  -- returns 'Un'
```

---

Spice.ai aims for compatibility with PostgreSQL, but some functions or behaviors may differ depending on the underlying engine version.