Regular-expression syntax and options

Applies to VAST Cluster 5.5.

Functions › Regular expressions › Regular expressions. The regular-expression functions (regexp_matches, regexp_full_match and ~, regexp_extract, regexp_extract_all, regexp_replace and the split functions) share one pattern syntax and one options string. This page describes both, and the pattern features that aren't available.

Syntax

A pattern and an options string are arguments of these functions:

regexp_matches(s, pattern)
regexp_matches(s, pattern, options)
regexp_full_match(s, pattern, options)
regexp_extract(s, pattern, group, options)
regexp_extract_all(s, pattern, group, options)
regexp_replace(s, pattern, replacement, options)

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_syntax. 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 five log lines. Line 4 has a line break in it, 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_syntax"'

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}.logs"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, line VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'ERROR 2026-10-16 disk full'), "
    "(2, 'warn: Disk 93% full'), "
    "(3, 'caf\u00e9 cr\u00e8me'), "
    "(4, 'Line1\nLine2'), "
    "(5, NULL)")

[]
[]
[(5,)]

What it does

Writing a pattern. In a SQL string literal a backslash is an ordinary character, so '\d+' in SQL is the pattern \d+. In a Python string, write it '\\d+', as on this page.

The options string is a constant of one-letter flags:

Options are lower-case: 'I', 'G', 'x' and any other letter fail with Unrecognized Regex option.

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_matches(NULL, 'a'), regexp_matches('abc', NULL), regexp_matches('abc', ''), regexp_matches('', '')")
run("SELECT regexp_matches('abc', 'b', NULL)")
run(f"SELECT regexp_matches(line, 'a', CAST(id AS VARCHAR)) FROM {TABLE} LIMIT 1")
run("SELECT regexp_matches('ab', '(?i)A(?-i)b'), regexp_matches('ABC', '(?i)b', 'c'), "
    "regexp_matches('ABC', 'b', 'ic'), regexp_matches('ABC', 'b', 'ci')")
run("SELECT regexp_matches('a.c', 'a.c', 'l'), regexp_matches('abc', 'a.c', 'l'), "
    "regexp_matches('e\u0301', '^.$'), regexp_matches('e\u0301', '^..$')")

[(None, None, True, True)]
InternalError: Invalid Input Error: Regex options field must not be NULL
InternalError: Invalid Input Error: Regex options field must be a constant
[(True, True, False, True)]
[(True, False, False, True)]

Examples

Character classes: \d for digits, \w and \pL for letters. \w+$ doesn't match the last word of café crème, because \w is ASCII only; \pL+$ does:

run(f"SELECT id, regexp_matches(line, '\\d{{4}}-\\d{{2}}-\\d{{2}}'), regexp_matches(line, '\\s\\w+$'), "
    f"regexp_matches(line, '\\s\\pL+$') FROM {TABLE} ORDER BY id LIMIT 10")

[(1, True, True, True), (2, False, True, True), (3, False, False, True), (4, False, True, False), (5, None, None, None)]

Options and inline flags: 'disk' with 'i' also matches Disk. In line 4, ^Line2 matches only with (?m), and 1.L (a dot for the line break) only with 's':

run(f"SELECT id, regexp_matches(line, 'disk', 'i'), regexp_matches(line, '^Line2'), "
    f"regexp_matches(line, '(?m)^Line2'), regexp_matches(line, '1.L'), regexp_matches(line, '1.L', 's') "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, True, False, False, False, False), (2, True, False, False, False, False), (3, False, False, False, False, False), (4, False, False, True, False, True), (5, None, None, None, None, None)]

How it runs in VAST

An invalid constant pattern fails the query with Invalid Input Error: and the reason, such as missing ): (. A pattern from a column is checked for each row: one invalid value fails the query, with the reason but without the Invalid Input Error: prefix. regexp_extract and regexp_replace behave differently with an invalid constant pattern: see their pages.

Patterns are always evaluated in the Query Engine. Before it plans a query, the Query Engine rewrites some regexp_matches patterns into cheaper tests:

The plans of a whole-string pattern, a prefix and a general pattern:

plan(f"SELECT id FROM {TABLE} WHERE regexp_matches(line, '^Hello World$')")
plan(f"SELECT id FROM {TABLE} WHERE regexp_matches(line, '^ERROR')")
plan(f"SELECT id FROM {TABLE} WHERE regexp_matches(line, '\\d+')")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│   prefix(line, 'ERROR')   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            line           │
│             id            │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│regexp_matches(line, '\d+')│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            line           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Prefer a literal or anchored pattern. '^ERROR' runs as prefix() and 'disk' as contains(); a whole-string pattern is pushed down as an equality.
• Use a range for a prefix. line >= 'ERROR' AND line < 'ERROS' is pushed down; see LIKE.
• Use a constant pattern. A mistake in it fails the query whatever the data, instead of only when a bad value turns up.
• Extract at load time. If you filter on a part of a string often, store it in its own column with regexp_extract when you write the data.

Limitations and errors

These pattern features aren't available. A constant pattern that uses one fails when the query is planned:

Other pattern errors name the problem: missing ): (, unexpected ):, missing ]: [, no argument for repetition operator: *, trailing \, invalid escape sequence: \\k.

Nested repetition is safe to use: (a*)*c on a string of 50,001 characters returns false without error.

run("SELECT regexp_matches('abab', '(ab)\\1')")
run("SELECT regexp_matches('ab', 'a(?=b)')")
run("SELECT regexp_matches('ab', '(?<x>a)b'), regexp_matches('ab', '(?P<x>a)b')")
run("SELECT regexp_matches('abc', 'b', 'x')")
run("SELECT regexp_matches('abc', '(')")
run("SELECT regexp_matches(repeat('a', 50000) || 'b', '(a*)*c')")

InternalError: Invalid Input Error: invalid escape sequence: \\1
InternalError: Invalid Input Error: invalid perl operator: (?=
InternalError: Invalid Input Error: invalid perl operator: (?<
InternalError: Invalid Input Error: Unrecognized Regex option x
InternalError: Invalid Input Error: missing ): (
[(False,)]

Similar functions

The other pattern languages, and how each matches one character in 'abc':

run("SELECT 'abc' LIKE 'a_c', 'abc' GLOB 'a?c', 'abc' SIMILAR TO 'a.c', regexp_full_match('abc', 'a.c'), "
    "regexp_matches('abc', '^a.c$')")
run("SELECT regexp_matches('a.b', 'a.b', 'l'), regexp_matches('axb', 'a.b', 'l'), "
    "regexp_matches('axb', regexp_escape('a.b'))")

[(True, True, True, True, True)]
[(True, False, False)]

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.
• Search and split: plain-text search and the LIKE family.
• Where filters run and pushdowns.