--- title: "regexp_split_to_array" slug: "dev-database-reference-sql-functions-string-regexp-split-to-array" 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-array" --- > ## 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_array *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 the parts as a list (`VARCHAR[]`). Use it when the separator varies, such as any run of spaces, or one of several characters. `string_split_regex` and `str_split_regex` are the same function. ## Syntax ``` regexp_split_to_array(s, pattern) regexp_split_to_array(s, pattern, options) string_split_regex(s, pattern[, options]) str_split_regex(s, pattern[, options]) ``` | 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. | | `options` | `VARCHAR`, a constant | Optional one-letter flags: `i` ignores case, `c` is case-sensitive (the default), `s` lets `.` match a newline, `l` treats the pattern as literal text. | **Returns:** `VARCHAR[]`, a list that the driver returns as a Python list. **Aliases:** `string_split_regex`, `str_split_regex`. ## 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_array`. 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 lists of values typed by hand: the separators are commas, semicolons or spaces, in any mix. Row 3 is a Windows path. 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_array"' 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}.entries" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, entry VARCHAR)") run(f"INSERT INTO {TABLE} VALUES " "(1, 'red, green;blue yellow'), " "(2, 'one;two'), " "(3, 'C:\\dir\\file.txt'), " "(4, NULL)") ``` ``` [] [] [(4,)] ``` ## What it does `regexp_split_to_array(s, pattern)` finds every match of the pattern, from left to right, and returns the text between the matches, in order. Nothing between two matches is dropped, so two separators side by side give an empty part, unless the pattern takes them as one match: | Call | Result | | --- | --- | | `regexp_split_to_array('a1b22c', '\d+')` | `['a', 'b', 'c']`: `\d+` takes `22` as one separator | | `regexp_split_to_array('a,b;c', '[,;]')` | `['a', 'b', 'c']`: either character | | `regexp_split_to_array('a b c', '\s+')` | `['a', 'b', 'c']` | | `regexp_split_to_array('a b c', ' ')` | `['a', 'b', '', 'c']`: each space is one separator | | `regexp_split_to_array(',a,', ',')` | `['', 'a', '']`: a match at either end gives an empty part there | | `regexp_split_to_array('a1b', '(\d)')` | `['a', 'b']`: what a group captures isn't kept | | `regexp_split_to_array('abc', 'x')` | `['abc']`: no match, one part | | `regexp_split_to_array('abc', '')` | `['a', 'b', 'c']` | - **`.` is a pattern, not a dot.** `regexp_split_to_array('a.b', '.')` matches every character and returns `['', '', '', '']`. Write `'\.'` for a literal dot, or pass the option `l`. - **Options** change how the pattern matches: `regexp_split_to_array('aXbxc', 'x', 'i')` returns `['a', 'b', 'c']`, while the default (`c`) returns `['aXb', 'c']`. - **An empty pattern** splits the string into code points, not into what a reader sees as characters: `e` followed by a combining accent becomes `e` and the accent alone. - **The result is a list,** so the [list functions](/develop/docs/dev-database-reference-sql-functions-lists-and-structs) work on it: `[2]` takes the second part and `len()` counts them. ## NULLs and edge cases | Input | Result | | --- | --- | | `s` is NULL | `NULL` | | `pattern` is NULL | the whole string as the only part: `['a,b']` | | `options` is NULL | Error: `Regex options field must not be NULL` | | `''` (empty string), with any pattern | `['']` | | `regexp_split_to_array('e\u0301x', '')` | `['e', '\u0301', 'x']`: three code points | | `regexp_split_to_array('a\nb', '$')` (a newline in the string) | `['a\nb', '']`: `$` matches only at the very end | | `regexp_split_to_array('a\nb', 'a.b', 's')` | `['', '']`: with `s`, `.` matches the newline | | An invalid pattern such as `'('` | Error: `Invalid Input Error: missing ): (` | | Lookahead such as `'(?=b)'` | Error: `Invalid Input Error: invalid perl operator: (?=` | | `s` a number, date, boolean, `BLOB` or list, or a number as the pattern | Error: `No function matches the given name and argument types` | 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-array#set-up-the-examples): ``` run("SELECT regexp_split_to_array(NULL, ','), regexp_split_to_array('a,b', NULL), regexp_split_to_array('', ','), " "regexp_split_to_array('e\u0301x', '')") run("SELECT regexp_split_to_array('a1b22c', '\\d+'), regexp_split_to_array('a,b;c', '[,;]'), " "regexp_split_to_array('a b c', '\\s+'), regexp_split_to_array('a b c', ' '), regexp_split_to_array(',a,', ',')") run("SELECT regexp_split_to_array('a1b', '(\\d)'), regexp_split_to_array('abc', 'x'), regexp_split_to_array('abc', '')") run("SELECT regexp_split_to_array('a.b', '.'), regexp_split_to_array('a.b', '\\.'), regexp_split_to_array('a.b', '.', 'l')") run("SELECT regexp_split_to_array('aXbxc', 'x', 'i'), regexp_split_to_array('aXbxc', 'x', 'c'), " "regexp_split_to_array('a\nb', '$'), regexp_split_to_array('a\nb', 'a.b', 's')") run("SELECT regexp_split_to_array('a,b', ',', NULL)") run("SELECT regexp_split_to_array('a', '(')") run("SELECT regexp_split_to_array('ab', '(?=b)')") ``` ``` [(None, ['a,b'], [''], ['e', '́', 'x'])] [(['a', 'b', 'c'], ['a', 'b', 'c'], ['a', 'b', 'c'], ['a', 'b', '', 'c'], ['', 'a', ''])] [(['a', 'b'], ['abc'], ['a', 'b', 'c'])] [(['', '', '', ''], ['a', 'b'], ['a', 'b'])] [(['a', 'b', 'c'], ['aXb', 'c'], ['a\nb', ''], ['', ''])] InternalError: Invalid Input Error: Regex options field must not be NULL InternalError: Invalid Input Error: missing ): ( InternalError: Invalid Input Error: invalid perl operator: (?= ``` ## Examples Split each entry on any run of commas, semicolons and spaces, and on either kind of slash for the path. In Python, `'\\s'` is the SQL text `\s`, and `'[/\\\\]'` is `[/\\]`, a class of `/` and a backslash: ``` run(f"SELECT id, regexp_split_to_array(entry, '[,;\\s]+'), regexp_split_to_array(entry, '[/\\\\]') " f"FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, ['red', 'green', 'blue', 'yellow'], ['red, green;blue yellow']), (2, ['one', 'two'], ['one;two']), (3, ['C:\\dir\\file.txt'], ['C:', 'dir', 'file.txt']), (4, None, None)] ``` The three names are the same function. Count the values, and take the second: ``` run(f"SELECT id, string_split_regex(entry, '[,;\\s]+'), len(str_split_regex(entry, '[,;\\s]+')), " f"regexp_split_to_array(entry, '[,;\\s]+')[2] FROM {TABLE} WHERE id <= 2 ORDER BY id LIMIT 10") ``` ``` [(1, ['red', 'green', 'blue', 'yellow'], 4, 'green'), (2, ['one', 'two'], 2, 'two')] ``` ## 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`). VAST DataBase can evaluate a comparison of a column with a constant, but not a regular expression, so `regexp_split_to_array()` is always computed by the Query Engine: - **In the `SELECT` list,** the scan reads the column and the Query Engine splits it for each row it returns. - **In `WHERE`,** the scan sends the column for every row, and a `FILTER` step in the Query Engine splits it and keeps the rows that match. No rows are skipped in storage. The plan of a filter on the number of values: ``` plan(f"SELECT id FROM {TABLE} WHERE len(regexp_split_to_array(entry, '[,;\\s]+')) > 2") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (len(regexp_split_to_array│ │ (entry, '[,;\s]+')) > 2) │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ entry │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Use `string_split()` for a fixed separator.** When the separator is always the same text, [string_split](/develop/docs/dev-database-reference-sql-functions-string-string-split) needs no regular expression. - **Split once, at load time.** If you often query the parts, store them as a `VARCHAR[]` column, or as one row per value, when you write the data. - **Combine it with a pushed-down condition.** In `id <= 2 AND len(regexp_split_to_array(entry, '[,;\\s]+')) > 2`, `id <= 2` is still evaluated in VAST DataBase, so the split runs only on the rows that pass it. ``` run(f"SELECT id, string_split(entry, ';'), regexp_split_to_array(entry, ';') FROM {TABLE} WHERE id = 2 LIMIT 10") plan(f"SELECT id FROM {TABLE} WHERE id <= 2 AND len(regexp_split_to_array(entry, '[,;\\s]+')) > 2") ``` ``` [(2, ['one', 'two'], ['one', 'two'])] ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #0 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (len(regexp_split_to_array│ │ (entry, '[,;\s]+')) > 2) │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ id │ │ entry │ │ │ └───────────────────────────┘ ``` ## Limitations and errors - **Options must be a constant.** Options taken from a column fail with `Regex options field must be a constant`, and NULL options with `Regex options field must not be NULL`. - **Only some options apply.** `g` fails with `Option 'g' (global replace) is only valid for regexp_replace`; a letter that isn't an option, such as `z`, fails with `Unrecognized Regex option z`. - **No lookahead.** `'(?=b)'` fails with `invalid perl operator: (?=`. See [regular expression syntax](/develop/docs/dev-database-reference-sql-functions-string-regexp-syntax). - **`VARCHAR` only.** A number, date, boolean, `BLOB` or list fails with `No function matches the given name and argument types 'regexp_split_to_array(INTEGER_LITERAL, STRING_LITERAL)'` (with the type you passed). Cast it to `VARCHAR` first. - **No `regexp_split`.** It fails with `Scalar Function with name regexp_split does not exist!`. Use `regexp_split_to_array`, or [regexp_split_to_table](/develop/docs/dev-database-reference-sql-functions-string-regexp-split-to-table) for one row for each part. ``` run("SELECT regexp_split_to_array('a,b', ',', 'g')") run("SELECT regexp_split_to_array('a b', '\\s', 'z')") run("SELECT regexp_split_to_array(CAST(10203 AS VARCHAR), '0')") run("SELECT regexp_split('a,b', ',')") ``` ``` InternalError: Invalid Input Error: Option 'g' (global replace) is only valid for regexp_replace InternalError: Invalid Input Error: Unrecognized Regex option z [(['1', '2', '3'],)] InternalError: Catalog Error: Scalar Function with name regexp_split does not exist! ``` ## Similar functions | Function | Splits on | Returns | Result for `'a1b22c'` | | --- | --- | --- | --- | | `regexp_split_to_array(s, '\d+')` | a regular expression | a list | `['a', 'b', 'c']` | | [regexp_split_to_table](/develop/docs/dev-database-reference-sql-functions-string-regexp-split-to-table) `(s, '\d+')` | a regular expression | one row for each part | `'a'`, `'b'`, `'c'` | | [string_split](/develop/docs/dev-database-reference-sql-functions-string-string-split) `(s, '2')` | literal text | a list | `['a1b', '', 'c']` | | [regexp_extract_all](/develop/docs/dev-database-reference-sql-functions-string-regexp-extract-all) `(s, '\d+')` | keeps the matches instead | a list | `['1', '22']` | ``` run("SELECT regexp_split_to_array('a1b22c', '\\d+'), string_split('a1b22c', '2'), regexp_extract_all('a1b22c', '\\d+')") run("SELECT regexp_split_to_table('a1b22c', '\\d+')") ``` ``` [(['a', 'b', 'c'], ['a1b', '', 'c'], ['1', '22'])] [('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_table](/develop/docs/dev-database-reference-sql-functions-string-regexp-split-to-table), [string_split](/develop/docs/dev-database-reference-sql-functions-string-string-split) and [regular expression syntax](/develop/docs/dev-database-reference-sql-functions-string-regexp-syntax). - [List functions](/develop/docs/dev-database-reference-sql-functions-lists-and-structs). - [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns). - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.