regexp_split_to_table

Applies to VAST Cluster 5.5.

Functions › 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)

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 returns the same parts as a list.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (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), 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, "<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 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

The same cases, as the cluster returns them. They use the table and helpers from 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 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 and 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

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: all the regular expression functions.
• regexp_split_to_array, string_split and unnest.
• Search and split: rows from a split.
• Pushdowns.
• EXPLAIN: how to read a plan.