regexp_extract

Applies to VAST Cluster 5.5.

Functions › Regular expressions › Regular expressions. regexp_extract(s, pattern) returns the first part of s that the regular expression pattern matches, or an empty string if nothing matches. With a group number it returns that group of the match; with a list of names it returns a STRUCT with one field per group.

Syntax

regexp_extract(s, pattern)
regexp_extract(s, pattern, group)
regexp_extract(s, pattern, group, options)
regexp_extract(s, pattern, names)
regexp_extract(s, pattern, names, options)

Returns: VARCHAR; with names, a STRUCT with one VARCHAR field per name.

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_extract. 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 file paths. Path 3 has no date, and 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_extract"'

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}.files"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, path VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '/data/2026-10-16/app.log'), "
    "(2, '/data/2026-10-17/db.csv'), "
    "(3, '/tmp/README'), "
    "(4, NULL)")

[]
[]
[(4,)]

What it does

regexp_extract finds the first match of pattern in s, then returns:

Without a match, the result is an empty string, not NULL; with names, every field is an empty string. A group that doesn't take part in the match, such as (x)? in '(x)?b', also gives ''. Groups are numbered by their opening parenthesis; (?:...) doesn't count, and a named group (?P<n>...) counts like any other. Read a field of the STRUCT with .name or ['name'].

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_extract(NULL, 'a'), regexp_extract('abc', NULL), regexp_extract('abc', '(a)', NULL), "
    "regexp_extract('abc', 'x'), regexp_extract('abc', '(a)', 2)")
run("SELECT regexp_extract('abc', '(b)', 1, NULL)")
run("SELECT regexp_extract('abc', '(a)', 10)")
run("SELECT regexp_extract('e\u0301x', '^.'), regexp_extract('abc', '(')")
run("SELECT regexp_extract('2026-10', '(\\d+)', ['y', 'm'])")
run("SELECT regexp_extract('2026-10', '(\\d+)-(\\d+)', ['y'])")

[(None, None, '', '', '')]
InternalError: Invalid Input Error: Regex options field must not be NULL
InternalError: Invalid Input Error: Group index must be between 0 and 9!
[('e', '')]
InternalError: Binder Error: Not enough group names in regexp_extract
[({'y': '2026'},)]

Examples

The date in each path, its month (group 2), and the file extension. Path 3 has no date, so the first two are empty strings:

run(f"SELECT id, regexp_extract(path, '\\d{{4}}-\\d{{2}}-\\d{{2}}'), "
    f"regexp_extract(path, '(\\d{{4}})-(\\d{{2}})', 2), regexp_extract(path, '\\.(\\w+)$', 1) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '2026-10-16', '10', 'log'), (2, '2026-10-17', '10', 'csv'), (3, '', '', ''), (4, None, None, None)]

Split the date into a STRUCT with named fields, and read one field:

run(f"SELECT id, regexp_extract(path, '(\\d+)-(\\d+)-(\\d+)', ['y', 'm', 'd']), "
    f"regexp_extract(path, '(\\d+)-(\\d+)-(\\d+)', ['y', 'm', 'd']).m FROM {TABLE} ORDER BY id LIMIT 10")

[(1, {'y': '2026', 'm': '10', 'd': '16'}, '10'), (2, {'y': '2026', 'm': '10', 'd': '17'}, '10'), (3, {'y': '', 'm': '', 'd': ''}, ''), (4, None, None)]

Ignore case with option 'i':

run(f"SELECT id, regexp_extract(path, '/([a-z]+)$', 1), regexp_extract(path, '/([a-z]+)$', 1, 'i') "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '', ''), (2, '', ''), (3, '', 'README'), (4, None, None)]

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). regexp_extract is always evaluated in the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine extracts from each value it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step computes regexp_extract() and keeps the rows that match.

plan(f"SELECT id FROM {TABLE} WHERE regexp_extract(path, '\\d{{4}}') = '2026'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (regexp_extract(path, '  │
│     \d{4}') = '2026')     │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            path           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Extract at load time. If you filter on an extracted part often, such as the date of a path, extract it when you write the data and store it in its own column. A comparison of that column with a constant is pushed down to VAST DataBase.
• Test with a pattern, not an extraction. To keep the rows that contain a match, regexp_matches(path, '\d{4}') is rewritten into cheaper tests for simple patterns (regexp_matches); regexp_extract(path, ...) <> '' isn't.
• Extract several groups in one call. One call with a name list returns every group of one match.

Limitations and errors

• Group numbers 0 to 9 only, and a constant: a group from a column fails with Group specification field must be a constant!, and so does a name list from a column.
• Name a group with the list form. A string such as 'n' as the third argument fails with Could not choose a best candidate function; write ['n'].
• Name lists have rules. Empty: regexp_extract requires non-empty lists of capture names. A repeated name: Duplicate group name "y" in regexp_extract. A NULL name: NULL group name in regexp_extract. A typed NULL list such as NULL::VARCHAR[] fails with an internal error; pass a list literal.
• An invalid constant pattern returns '' instead of failing. Check a pattern with regexp_matches, which reports the error.
• No regexp_substr. It fails with Scalar Function with name regexp_substr does not exist!; use regexp_extract.

run("SELECT regexp_extract('abc', '(?P<n>b)', 'n')")
run("SELECT regexp_extract('abc', '(?P<n>b)', ['n'])")
run("SELECT regexp_extract('2026-10', '(\\d+)-(\\d+)', ['y', 'y'])")
run("SELECT regexp_matches('abc', '(')")
run("SELECT regexp_substr('abc', 'b')")

InternalError: Binder Error: Could not choose a best candidate function for the function call "regexp_extract(STRING_LITERAL, STRING_LITERAL, STRING_LITERAL)". In order to select one, please add explicit type casts.
[({'n': 'b'},)]
InternalError: Binder Error: Duplicate group name "y" in regexp_extract
InternalError: Invalid Input Error: missing ): (
InternalError: Catalog Error: Scalar Function with name regexp_substr does not exist!

Similar functions

run("SELECT regexp_extract('a,b,c', '^[^,]*,([^,]*)', 1), regexp_extract_all('a,b,c', '[^,]+'), "
    "split_part('a,b,c', ',', 2), string_split('a,b,c', ',')[2], regexp_matches('a,b,c', ',')")

[('b', ['a', 'b', 'c'], 'b', 'b', True)]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• Regular-expression syntax and options.
• Regular expressions: all the regular-expression functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.