regexp_split_to

Applies to VAST Cluster 5.5.

Functions › 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) 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), 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), 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, "<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 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:

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 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.
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 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 `(s, '\d+')`	a regular expression	one row for each part	`'a'`, `'b'`, `'c'`
string_split `(s, '2')`	literal text	a list	`['a1b', '', 'c']`
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()

[]
[]

regexp_split_to_array