sql

The sql template derives the tokens of a value by evaluating a scalar expression over it. The expression returns either one token or a list of tokens, which turns the built-in SQL functions into an analyzer: lower, trim, regexp_split_to_array, string_split, replace, a CASE, a cast. Use it for a transformation no other template offers — a split rule a single regular expression cannot express, a token derived from a structured value, or normalization with the same function the rest of the schema uses.

There are two spellings, and neither names the value. A call at a stage position takes the value as its first argument, so AS string_split(' ') splits on spaces and AS split_text_csv(',') | upper() upper-cases each token. A lambda names the value, for everything a call cannot express: an operator, a cast, a CASE, or a value that is not the first argument. Write it (lambda x: <expression>), as in (lambda x: x || '!') or (lambda x: nullif(x, 'skip')).

The parentheses around a lambda are needed whenever another stage follows it, because the body would otherwise swallow the rest of the chain. A few functions have their own SQL grammar and cannot be written with no arguments at all — trim() and nullif() are parse errors — so those need the lambda form too.

Options

Option	Type	Default	Description
EXPRESSION	string	required	Scalar expression over the value (VARCHAR). It returns VARCHAR or BLOB — one token per value — or a list of them, which is one token per element. Built-in functions only, no subqueries, no parameters, no volatile functions

The option is internal: a sql stage is written as a call or a lambda, never as sql(...), which fails with unknown stage "sql" because there is neither a template nor a function of that name.

A lambda parameter may appear any number of times. Nothing else is in scope, so a column reference fails.

CREATE TEXT SEARCH DICTIONARY parses and binds the expression once to validate it, so a malformed or disallowed expression is rejected by the statement that creates the dictionary:

• sql(): required option "expression" not given — the option is missing.
• sql: expected exactly one expression — the value holds more than one comma-separated expression.
• sql: <DuckDB parser message> — the value does not parse as an expression.
• "$1" is not available in an SQL stage — any placeholder, positional or named; column "input" is not available in an SQL stage — the pseudo-column earlier versions used. Both were replaced by the implicit first argument and the lambda.
• sql: subqueries are not allowed — a SELECT inside the expression.
• unknown stage "<name>" — a call at a stage position whose name is neither a template nor a function of any catalog. The hint suggests the nearest template name when there is one, and points a call on a stored dictionary's name at the bare-name form.
• sql: function "<name>": only built-in functions are allowed — the function is qualified with a catalog other than system or a schema other than main, or its name is not a scalar function, macro or aggregate of the system catalog. An unqualified name is resolved as system.main.<name>, which is why a user-defined macro is rejected whether it is qualified or not.
• sql: text-search function "<name>" is not allowed in a sql tokenizer expression (it would recurse into the tokenizer) — any function whose name starts with ts_, anywhere in the expression, since calling one would re-enter the tokenizer.
• sql: volatile expressions are not allowed — random()::VARCHAR and the like. The same value has to produce the same tokens every time.
• sql: expression must return VARCHAR, BLOB, or a list of them, got <type> — length() reports BIGINT, (lambda x: [length(x)]) reports BIGINT[].
• sql: <DuckDB binder message> — whatever else the binder refuses: an unknown column, a call whose argument types no overload of the function accepts, an aggregate, a window function, a lambda. The wording mentions check constraints, because the expression is bound the way a CHECK constraint is.

The template supports the FREQUENCY, POSITION and NORM feature flags. OFFSET is rejected with Unsupported index features are specified: offset, because an expression result carries no offsets back into the source value; a pipeline that contains a sql step loses offsets for the same reason.

Tokenization

The return type decides the token shape. A scalar VARCHAR or BLOB result is exactly one token per value; a list result — VARCHAR[] or BLOB[] — is one token per element, emitted in list order.

Token content is verbatim. The template applies no case folding, accent folding, stemming, stopword filtering or trimming, and imposes no length limit, truncation, padding or marker bytes. Whatever bytes the expression returns become the term, so casing and accents are entirely the expression's business — lower(), strip_accents(). The original value does not survive on its own, since only the result is emitted, unless the expression keeps it as (lambda x: list_value(x, lower(x))) does. An empty string the expression produces is emitted as an empty token; the template filters nothing.

Positions are dense: with POSITION enabled the tokens of a value are numbered consecutively from 1 in emission order, one per token, with no gaps and no stacked positions, so a phrase query matches the order the expression produced.

Input	EXPRESSION	Tokens
Hello, World! FOO bar	(lambda x: regexp_split_to_array(lower(x), '\W+'))	hello, world, foo, bar
Foo BAR baz	lower() | string_split(' ')	foo, bar, baz
hello	(lambda x: upper(trim(x)))	HELLO
Ab	(lambda x: list_value(upper(x), NULL, lower(x)))	AB, ab
skip	(lambda x: nullif(x, 'skip'))	(none — the value is rejected)
ab	(lambda x: x::BLOB)	one BLOB token holding the two bytes of ab

The expressions above are written as they read; inside the statement each single quote is doubled.

A NULL result rejects the value, which then contributes no tokens at all; ts_lexize and the tokenizer functions return NULL for a rejected value, while a value that is accepted but produces no tokens returns an empty array. A NULL element inside a list is dropped on its own and the surrounding elements are still emitted. An empty list accepts the value and produces no tokens. NULL rows never reach the expression. Watch for NULL constants in null-propagating functions: replace(input, NULL, 'x') is NULL for every value, so every value is rejected.

A BLOB-returning expression types the tokens as BLOB, and their bytes are indexed as raw byte terms — the template performs no UTF-8 validation or conversion of its own. ts_lexize then returns BLOB[], and the dictionary name has to be a constant: ts_lexize refuses a non-constant name for a dictionary that produces BLOB tokens.

An error raised while the expression runs — a failed cast, an invalid regular expression argument — is not caught by the template. It propagates out of the statement that was analyzing the value, whether that is INSERT, CREATE INDEX or ts_lexize.

As a step of a pipeline the expression runs once per token the previous step produced: that token is what the stage receives, and the result replaces it. A scalar result rewrites the token one-to-one, a list result fans it out.

Examples

A list result, one token per element

regexp_split_to_array splits the lowercased value on runs of non-word characters, and each element of the list becomes a token:

SQL

CREATE TEXT SEARCH DICTIONARY sql_words AS    (lambda x: regexp_split_to_array(lower(x), '\W+'));
SELECT ts_lexize('sql_words', 'Hello, World! FOO bar');

 ts_lexize
-----------------------
 {hello,world,foo,bar}

A scalar result, one token per value

A scalar expression is a normalizer: the whole value comes out as a single token, trimmed and uppercased.

SQL

CREATE TEXT SEARCH DICTIONARY sql_norm AS    (lambda x: upper(trim(x)));
SELECT ts_lexize('sql_norm', '  hello  ');

 ts_lexize
-----------
 {HELLO}

Inside a pipeline

A sql first step splits and lowercases, then a remove_stopwords second step filters the tokens it produced:

SQL

CREATE TEXT SEARCH DICTIONARY sql_pipe AS    (lambda x: string_split(lower(x), ' ')) | remove_stopwords(['the']);
SELECT ts_lexize('sql_pipe', 'The Quick Fox');
CREATE TEXT SEARCH DICTIONARY sql_copy AS    sql_words;
SELECT ts_lexize('sql_copy', 'A-B c');
CREATE TEXT SEARCH DICTIONARY sql_nullif AS    (lambda x: nullif(lower(x), 'skip'));
SELECT ts_lexize('sql_nullif', 'keep');
SELECT ts_lexize('sql_nullif', 'SKIP') IS NULL AS rejected, ts_lexize('sql_nullif', '') AS empty;
CREATE TEXT SEARCH DICTIONARY sql_recurse AS    (lambda x: ts_lexize('sql_recurse', x));
DROP TEXT SEARCH DICTIONARY sql_nullif;

 ts_lexize
-------------
 {quick,fox}

# DOCS_TEST_END

# a stored sql dictionary as a stage keeps its expression

 ts_lexize
-----------
 {a,b,c}

 ts_lexize
-----------
 {keep}

 rejected | empty
----------+-------
 t        | {""}

 # A sql tokenizer expression may not call text-search functions: they would
---------------------------------------------------------------------------------
 # re-enter the tokenizer machinery (unbounded recursion when self-referencing).

error is not allowed in a sql tokenizer expression

Expression form

The same two dictionaries written as expressions, with the SQL inline:

SQL

CREATE TEXT SEARCH DICTIONARY sql_expr_words AS (lambda x: regexp_split_to_array(lower(x), '\W+'));
SELECT ts_lexize('sql_expr_words', 'Hello, World! FOO bar');

 ts_lexize
-----------------------
 {hello,world,foo,bar}

SQL

CREATE TEXT SEARCH DICTIONARY sql_expr_pipe AS (lambda x: string_split(lower(x), ' ')) | remove_stopwords(['the']);
SELECT ts_lexize('sql_expr_pipe', 'The Quick Fox');

 ts_lexize
-------------
 {quick,fox}

See also

• split_by_pattern — split or extract with a regular expression, without a SQL expression
• filter_tokens — drop tokens with an SQL predicate while keeping their offsets
• keyword — keep the whole value as one verbatim token
• normalize_tokens — case and accent normalization as built-in options
• pipeline — chain sql with other analyzers
• CREATE TEXT SEARCH DICTIONARY