> ## Documentation Index
> Fetch the complete documentation index at: https://docs.lancedb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Scalar functions

> The per-row function set: math, string, regex, date/time, array, struct, and what Lance adds.

**Applies to:** Embedded ❌, Enterprise ✅

Functions that take a value and return a value, one row at a time. The set is
based on DataFusion's registration plus Lance and deployment-specific
functions. The names below are selected examples, not an exhaustive catalog
for every deployment. Availability depends on the deployed version and build.

For per-group functions see [Aggregation](https://docs.lancedb.com/sql/dql/aggregation.md); for per-window,
[Window functions](https://docs.lancedb.com/sql/dql/window.md).

## Math

```text theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
abs  ceil  floor  round  trunc  signum  power  sqrt  cbrt  exp
ln  log  log2  log10  factorial  gcd  lcm  pi  random  nanvl
isnan  iszero  cot  degrees  radians  to_degrees  to_radians
sin  cos  tan  asin  acos  atan  atan2
sinh  cosh  tanh  asinh  acosh  atanh
```

## String

```text theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
ascii  bit_length  btrim  chr  concat  concat_ws  contains  ends_with
starts_with  levenshtein  lower  upper  ltrim  rtrim  octet_length
repeat  replace  split_part  to_hex  uuid
```

Unicode-aware variants:

```text theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
character_length  find_in_set  initcap  left  right  lpad  rpad
reverse  strpos  substr  substr_index  substring  translate
```

> [!NOTE]
>
> For keyword matching on a text column, [`contains_tokens`](https://docs.lancedb.com/sql/dql/contains-tokens)
> is index-accelerated where `contains` and `LIKE` are not.

## Regular expressions

```text theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
regexp_count  regexp_instr  regexp_like  regexp_match  regexp_replace
```

## Date and time

```text theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
current_date  current_time  date_bin  date_part  date_trunc
from_unixtime  to_unixtime  make_date  make_time  to_char
to_date  to_time  to_local_time
```

`date_bin` is the one worth knowing for analytics: it buckets timestamps into
fixed-width intervals, which is how you build a time series without a
self-join.

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT date_bin(INTERVAL '1 hour', created_at) AS hour, count(*)
FROM events
GROUP BY hour
ORDER BY hour
```

## Arrays and maps

```text theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
make_array  array_has  array_filter  array_transform  array_any_match
array_compact  array_normalize  array_slice  arrays_zip  cardinality
flatten  range  generate_series
```

```text theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
map  map_keys  map_values  map_entries  map_extract
```

Distance functions over arrays: `cosine_distance`, `distance`,
`exact_distance`, `inner_product`. Only `distance` and `exact_distance`
take an options string, and only `distance` can plan as vector search; see
[Vector distance functions](https://docs.lancedb.com/sql/dql/vector-distance.md).

## Structs and fields

```text theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
named_struct  get_field  coalesce  nullif  nvl  nvl2  greatest  least
union_extract  union_tag
```

## Crypto and encoding

```text theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
digest  md5  sha224  sha256  sha384  sha512  encode  decode
```

## Type functions

```text theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
arrow_cast  arrow_try_cast  arrow_typeof  arrow_field  arrow_metadata
with_metadata  version
```

Use `arrow_typeof` to inspect the Arrow data type of a column or expression:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT arrow_typeof(vector) FROM documents LIMIT 1
-- FixedSizeList(Field { name: "item", data_type: Float32, .. }, 1536)
```

`arrow_cast` casts to an Arrow type by name, reaching types SQL has no spelling
for. See [Data types](https://docs.lancedb.com/sql/data-types.md).

## What Lance adds

These are registered by Lance on top of DataFusion's set, each with its own
reference page:

| | |
| - | - |
| [`contains_tokens`](https://docs.lancedb.com/sql/dql/contains-tokens) | index-accelerated keyword predicate, usable in `WHERE` |
| [`ivf_partition`](https://docs.lancedb.com/sql/dql/ivf-partition) | assign each row to its trained IVF index partition |
| [`blob_descriptor`](https://docs.lancedb.com/sql/dql/blob-descriptor) | a BlobV2 column's payload location, without reading it |

## Additional scalar functions

SQL queries can call functions registered with the server, including functions
not listed on this page. Consult
[DataFusion's scalar function reference](https://datafusion.apache.org/user-guide/sql/scalar_functions.html)
for version 54. Functions documented for a newer DataFusion release may not be
available in your LanceDB Enterprise version.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.