--- title: "regexp_split_to_table" slug: "dev-database-reference-sql-functions-string-regexp-split-to-table" updated: 2026-10-30T18:18:56Z published: 2026-10-30T18:18:56Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-string-regexp-split-to-table" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # regexp_split_to_table *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Regular expressions](/develop/docs/dev-database-reference-sql-functions-string-regular-expressions) › Splitting.* Splits a string at every match of a regular expression and returns **one row for each part**. It goes in the `SELECT` list, where the other columns repeat on each row; it isn't a table function for `FROM`. It's the same as `unnest(regexp_split_to_array(s, pattern))`. ## Syntax ``` regexp_split_to_table(s, pattern) ``` | Argument | Type | Description | | --- | --- | --- | | `s` | `VARCHAR` | The string to split. | | `pattern` | `VARCHAR` | A regular expression ([syntax](/develop/docs/dev-database-reference-sql-functions-string-regexp-syntax)) for the separator. It can be a constant or a column. An empty pattern splits into single code points. | **Returns:** one `VARCHAR` row for each part. **Related:** `regexp_split_to_table(s, pattern)` runs as `unnest(string_split_regex(s, pattern))`; [regexp_split_to_array](/develop/docs/dev-database-reference-sql-functions-string-regexp-split-to-array) returns the same parts as a list. ## Set up the examples The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-adbc), and work in a schema called `docs_sql_fn_string_regexp_split_to_table`. Set `QE_ENDPOINT`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` and `VASTDB_BUCKET` ([SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), and run the blocks in order in one Python session. Below each block is the output you should see. This block connects, defines the `run()` and `plan()` helpers, and creates a table of four articles, each with keywords separated by commas, semicolons or spaces, in any mix. The last row has no values: ``` import os import re import adbc_driver_manager import adbc_driver_manager.dbapi as dbapi import adbc_driver_vastdb BUCKET = os.environ["VASTDB_BUCKET"] SCHEMA = f'"{BUCKET}/docs_sql_fn_string_regexp_split_to_table"' conn = dbapi.connect( driver=adbc_driver_vastdb.get_driver_path(), db_kwargs={ "vast.db.endpoint": os.environ["QE_ENDPOINT"], "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"], "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"], }, autocommit=True, ) def server_message(e): """The server's message in a driver error, without the request ID or the echoed statement.""" text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e)) found = (re.search(r'message: "(.*?)", request_id', text, re.S) or re.search(r"description: (.*)\)", text, re.S)) message = found.group(1) if found else text.strip().splitlines()[-1] message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S) message = re.split(r"\\n|\n", message)[0].replace('\\"', '"') return message.replace(BUCKET, "") def run(sql): """Run one statement. Print the rows it returns, or the error.""" try: with conn.cursor() as cur: cur.execute(sql) rows = cur.fetchall() if cur.description else [] except (adbc_driver_manager.Error, OSError) as e: print(f"{type(e).__name__}: {server_message(e)}") else: print(rows) def plan(sql): """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert.""" with conn.cursor() as cur: cur.execute("EXPLAIN " + sql) steps = cur.fetchall()[0][1] print("\n".join(line for line in steps.splitlines() if "Rows" not in line)) TABLE = f"{SCHEMA}.articles" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, keywords VARCHAR)") run(f"INSERT INTO {TABLE} VALUES " "(1, 'sql, vector;search'), " "(2, 'kafka streaming'), " "(3, ''), " "(4, NULL)") ``` ``` [] [] [(4,)] ``` ## What it does `regexp_split_to_table(s, pattern)` splits the string exactly as [regexp_split_to_array](/develop/docs/dev-database-reference-sql-functions-string-regexp-split-to-array) does, then returns each part as a row of its own: - **The other columns repeat** on each of a string's rows: `SELECT id, regexp_split_to_table(keywords, '[,;\s]+')` returns `id` once for each keyword. - **Empty parts are rows too.** A separator at either end, or two separators the pattern doesn't take as one match, gives an empty-string row; an empty string gives one empty-string row. - **A NULL string gives no rows,** so that source row disappears from the result. - **Two in one `SELECT` list are lined up side by side,** first part with first part; the shorter one is padded with NULL. ## NULLs and edge cases | Input | Result | | --- | --- | | `s` is NULL | no rows | | `pattern` is NULL | one row: the whole string | | `''` (empty string) | one row, `''` | | `regexp_split_to_table(',a,', ',')` | three rows: `''`, `'a'`, `''` | | `regexp_split_to_table('abc', '')` | three rows: `'a'`, `'b'`, `'c'` | | `regexp_split_to_table('a,b', ','), regexp_split_to_table('x,y,z', ',')` | `('a', 'x')`, `('b', 'y')`, `(None, 'z')` | | A third argument (options) | Error: `Macro function regexp_split_to_table(text, pattern) requires 2 positional arguments, but 3 positional arguments were provided.` | | An invalid pattern such as `'('` | Error: `Invalid Input Error: missing ): (` | | `s` a number, date, boolean, `BLOB` or list | Error: `No function matches the given name and argument types 'string_split_regex(...)'` | The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-string-regexp-split-to-table#set-up-the-examples): ``` run("SELECT regexp_split_to_table(NULL, ',')") run("SELECT regexp_split_to_table('a,b', NULL)") run("SELECT regexp_split_to_table('', ',')") run("SELECT regexp_split_to_table(',a,', ',')") run("SELECT regexp_split_to_table('abc', '')") run("SELECT regexp_split_to_table('a,b', ','), regexp_split_to_table('x,y,z', ',')") run("SELECT regexp_split_to_table('aXbxc', 'x', 'i')") run("SELECT regexp_split_to_table('a', '(')") run("SELECT regexp_split_to_table(123, ',')") ``` ``` [] [('a,b',)] [('',)] [('',), ('a',), ('',)] [('a',), ('b',), ('c',)] [('a', 'x'), ('b', 'y'), (None, 'z')] InternalError: Binder Error: Macro function regexp_split_to_table(text, pattern) requires 2 positional arguments, but 3 positional arguments were provided. InternalError: Invalid Input Error: missing ): ( InternalError: Binder Error: No function matches the given name and argument types 'string_split_regex(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts. ``` ## Examples One row for each keyword of the first article. In Python, `'\\s'` is the SQL text `\s`: ``` run(f"SELECT id, regexp_split_to_table(keywords, '[,;\\s]+') AS keyword FROM {TABLE} WHERE id = 1 LIMIT 10") ``` ``` [(1, 'sql'), (1, 'vector'), (1, 'search')] ``` Count the keywords of all the articles: the empty string of row 3 counts as one, and the NULL of row 4 as none: ``` run(f"SELECT count(*) FROM (SELECT regexp_split_to_table(keywords, '[,;\\s]+') FROM {TABLE})") ``` ``` [(6,)] ``` For a case-insensitive split, use `unnest()` on [regexp_split_to_array](/develop/docs/dev-database-reference-sql-functions-string-regexp-split-to-array) with the `i` option: ``` run("SELECT unnest(regexp_split_to_array('aXbxc', 'x', 'i'))") ``` ``` [('a',), ('b',), ('c',)] ``` ## How it runs in VAST The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (`VAST_SCAN`). The scan reads the column; above it, the Query Engine splits each value with `string_split_regex()` and an `UNNEST` step turns each list into rows. Nothing about the split is pushed down. A `WHERE` condition on another column, such as `id = 1`, is evaluated before the split, so only those rows are split. ``` plan(f"SELECT id, regexp_split_to_table(keywords, '[,;\\s]+') FROM {TABLE} WHERE id = 1") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ id │ │ UNNEST(string_split_regex │ │ (keywords, '[,;\s]+')) │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ UNNEST │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ id │ │ keywords │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Filter the source rows first.** A condition on the table's columns, such as `id = 1` or a time range, is evaluated before the split, often in VAST DataBase, so fewer strings are split. - **Use `string_split()` for a fixed separator.** `unnest(string_split(keywords, ','))` gives one row for each part without a regular expression. - **Split once, at load time.** If you often query the parts as rows, store them as rows when you write the data. ``` run(f"SELECT id, unnest(string_split(keywords, ',')) AS keyword FROM {TABLE} WHERE id = 1 LIMIT 10") ``` ``` [(1, 'sql'), (1, ' vector;search')] ``` ## Limitations and errors - **Only in the `SELECT` list.** In `WHERE` it fails with `Binder Error: UNNEST not supported here`; in `FROM`, `SELECT * FROM regexp_split_to_table(...)` fails with `Table Function with name regexp_split_to_table does not exist!`. To filter on the parts, split in a subquery and filter outside it, or test the list with [regexp_split_to_array](/develop/docs/dev-database-reference-sql-functions-string-regexp-split-to-array) and [list_contains](/develop/docs/dev-database-reference-sql-functions-lists-list-contains). - **Two arguments only.** Options fail with `Macro function regexp_split_to_table(text, pattern) requires 2 positional arguments, but 3 positional arguments were provided.` Use `unnest(regexp_split_to_array(s, pattern, options))`. - **Sorting needs a `LIMIT`.** `ORDER BY` without `LIMIT` fails with `The operator 'ORDER_BY' type=1 is currently not supported for this usage.` Add a `LIMIT`. With `ORDER BY id`, the parts of one source row can come back in a different order from the string. - **`VARCHAR` only.** A number, date, boolean, `BLOB` or list fails with `No function matches the given name and argument types 'string_split_regex(INTEGER_LITERAL, STRING_LITERAL)'` (with the type you passed). Cast it to `VARCHAR` first. ``` run(f"SELECT id FROM {TABLE} WHERE regexp_split_to_table(keywords, ',') = 'sql'") run("SELECT * FROM regexp_split_to_table('a,b', ',')") run(f"SELECT id, kw FROM (SELECT id, regexp_split_to_table(keywords, '[,;\\s]+') AS kw FROM {TABLE}) " f"WHERE kw = 'search' LIMIT 10") run(f"SELECT id FROM {TABLE} WHERE list_contains(regexp_split_to_array(keywords, '[,;\\s]+'), 'search') " f"ORDER BY id LIMIT 10") ``` ``` InternalError: Binder Error: UNNEST not supported here InternalError: Catalog Error: Table Function with name regexp_split_to_table does not exist! [(1, 'search')] [(1,)] ``` ## Similar functions | Function | Splits on | Returns | | --- | --- | --- | | `regexp_split_to_table(s, pattern)` | a regular expression | one row for each part | | [regexp_split_to_array](/develop/docs/dev-database-reference-sql-functions-string-regexp-split-to-array) | a regular expression | a list | | `unnest(`[string_split](/develop/docs/dev-database-reference-sql-functions-string-string-split)`(s, sep))` | literal text | one row for each part | | [unnest](/develop/docs/dev-database-reference-sql-functions-table-functions-unnest) | | one row for each element of any list | ``` run("SELECT regexp_split_to_table('a1b22c', '\\d+')") run("SELECT regexp_split_to_array('a1b22c', '\\d+')") run("SELECT unnest(string_split('a,b,c', ','))") ``` ``` [('a',), ('b',), ('c',)] [(['a', 'b', 'c'],)] [('a',), ('b',), ('c',)] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [Regular expressions](/develop/docs/dev-database-reference-sql-functions-string-regular-expressions): all the regular expression functions. - [regexp_split_to_array](/develop/docs/dev-database-reference-sql-functions-string-regexp-split-to-array), [string_split](/develop/docs/dev-database-reference-sql-functions-string-string-split) and [unnest](/develop/docs/dev-database-reference-sql-functions-table-functions-unnest). - [Search and split](/develop/docs/dev-database-reference-sql-functions-string-search-and-split): rows from a split. - [Pushdowns](/develop/docs/dev-database-reference-pushdowns). - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.