Scalar functions

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; for per-window, Window functions.

Math

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

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:

character_length  find_in_set  initcap  left  right  lpad  rpad
reverse  strpos  substr  substr_index  substring  translate

For keyword matching on a text column, contains_tokens is index-accelerated where contains and LIKE are not.

Regular expressions

regexp_count  regexp_instr  regexp_like  regexp_match  regexp_replace

Date and time

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.

SELECT date_bin(INTERVAL '1 hour', created_at) AS hour, count(*)
FROM events
GROUP BY hour
ORDER BY hour

Arrays and maps

make_array  array_has  array_filter  array_transform  array_any_match
array_compact  array_normalize  array_slice  arrays_zip  cardinality
flatten  range  generate_series

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.

Structs and fields

named_struct  get_field  coalesce  nullif  nvl  nvl2  greatest  least
union_extract  union_tag

Crypto and encoding

digest  md5  sha224  sha256  sha384  sha512  encode  decode

Type functions

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:

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.

What Lance adds

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


`contains_tokens`	index-accelerated keyword predicate, usable in `WHERE`
`ivf_partition`	assign each row to its trained IVF index partition
`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 for version 54. Functions documented for a newer DataFusion release may not be available in your LanceDB Enterprise version.

Introduction

Getting Started

Concepts

Data Ingestion

Search

Data Curation

Feature Engineering

Training

Management

Maintenance

Security

Deployment

SQL Reference

Scalar functions

Math

String

Regular expressions

Date and time

Arrays and maps

Structs and fields

Crypto and encoding

Type functions

What Lance adds

Additional scalar functions

​Math

​String

​Regular expressions

​Date and time

​Arrays and maps

​Structs and fields

​Crypto and encoding

​Type functions

​What Lance adds

​Additional scalar functions

Math

String

Regular expressions

Date and time

Arrays and maps

Structs and fields

Crypto and encoding

Type functions

What Lance adds

Additional scalar functions