regexp_full

Applies to VAST Cluster 5.5.

Functions › Regular expressions › Regular expressions. regexp_full_match(s, pattern) is true if the regular expression pattern matches all of s, from the first character to the last. s ~ pattern is the same test, and s !~ pattern its negation.

Syntax

regexp_full_match(s, pattern)
regexp_full_match(s, pattern, options)
s ~ pattern
s !~ pattern

Argument	Type	Description
`s`	`VARCHAR`	The string to test.
`pattern`	`VARCHAR`	The regular expression (syntax). A constant or a column.
`options`	`VARCHAR`	Optional, a constant. Flags such as `'i'` (ignore case) or `'s'` (`.` matches a line break); see options. The operators take no options.

Returns: BOOLEAN.

Aliases: ~ (regexp_full_match(s, pattern)), !~ (NOT regexp_full_match(s, pattern)).

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_full_match. 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 event codes. 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_full_match"'

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}.events"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, code VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'ERR-001'), "
    "(2, 'ERR-1002'), "
    "(3, 'WARN-001'), "
    "(4, 'err-003'), "
    "(5, NULL)")

[]
[]
[(5,)]

What it does

The pattern must match the whole string, as if it were written ^(pattern)$:

Call	Result	Why
`regexp_full_match('abc', 'b')`	`false`	`b` is only part of the string
`regexp_full_match('abc', '.b.')`	`true`	the pattern covers the whole string
`regexp_full_match('abc', 'ab\\|abc')`	`true`	an alternative that matches the whole string is used
`regexp_full_match('ABC', 'abc', 'i')`	`true`	option `i` ignores case
`'ABC' ~ '(?i)abc'`	`true`	the operator takes no options; use an inline flag

A trailing line break is part of the string: 'a' followed by a line break doesn't fully match 'a' or 'a$'. The pattern syntax and the options are described on Regular-expression syntax and options.

NULLs and edge cases

Input	Result
A `NULL` string or pattern	`NULL`
A `NULL` options string	Error: `Regex options field must not be NULL`
`regexp_full_match('', '')`, `regexp_full_match('', '.*')`	`true`
`regexp_full_match('abc', '')`	`false`
`regexp_full_match('é', '.')` (precomposed), `regexp_full_match('e\u0301', '.')`	`true`, `false`
`'abc' !~ '.*'`	`false`
An invalid constant pattern: `'abc' ~ '('`	Error: `missing ): (`
`'abc' ~ 1`	Error: `No function matches the given name and argument types 'regexp_full_match(STRING_LITERAL, INTEGER_LITERAL)'`

The same cases, as the cluster returns them. They use the table and helpers from Set up the examples:

run("SELECT regexp_full_match(NULL, 'a'), 'abc' ~ NULL, NULL !~ 'a', regexp_full_match('', ''), "
    "regexp_full_match('', '.*'), regexp_full_match('abc', '')")
run("SELECT regexp_full_match('abc', 'abc', NULL)")
run("SELECT regexp_full_match('\u00e9', '.'), regexp_full_match('e\u0301', '.'), 'abc' !~ '.*'")
run("SELECT 'abc' ~ '('")
run("SELECT 'abc' ~ 1")

[(None, None, None, True, True, False)]
InternalError: Invalid Input Error: Regex options field must not be NULL
[(True, False, False)]
InternalError: Invalid Input Error: missing ): (
InternalError: Binder Error: No function matches the given name and argument types 'regexp_full_match(STRING_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

Which codes have the form ERR- and three digits: ERR-1002 has four, and err-003 is lower-case unless you add option i. !~ gives the opposite:

run(f"SELECT id, regexp_full_match(code, 'ERR-\\d{{3}}'), regexp_full_match(code, 'ERR-\\d{{3}}', 'i'), "
    f"code ~ '[A-Z]+-\\d+', code !~ '[A-Z]+-\\d+' FROM {TABLE} ORDER BY id LIMIT 10")

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

Keep the rows whose code is well formed, in any case:

run(f"SELECT id, code FROM {TABLE} WHERE regexp_full_match(code, '[a-z]+-\\d{{3}}', 'i') ORDER BY id LIMIT 10")

[(1, 'ERR-001'), (3, 'WARN-001'), (4, 'err-003')]

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_full_match is always evaluated in the Query Engine: in WHERE, it's a FILTER step above the scan, which sends the column for every row. ~ appears in plans as regexp_full_match, and !~ as NOT regexp_full_match.

When the pattern starts with fixed text, the Query Engine adds a second FILTER step before the pattern test: a range on the column (cast to BLOB in the plan) that covers the strings the pattern can match. Both steps run in the Query Engine; the range isn't pushed down to the scan.

plan(f"SELECT id FROM {TABLE} WHERE code ~ 'ERR-\\d+'")
plan(f"SELECT id FROM {TABLE} WHERE code !~ 'ERR-\\d+'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  regexp_full_match(code,  │
│         'ERR-\d+')        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  ((code >= 'ERR-0'::BLOB) │
│   AND (code <= 'ERR-99:': │
│          :BLOB))          │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            code           │
│             id            │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│   (NOT regexp_full_match  │
│     (code, 'ERR-\d+'))    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            code           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

Use = for a fixed value. regexp_full_match(code, 'ERR-001') is a FILTER step; code = 'ERR-001' is pushed down to VAST DataBase. regexp_matches with '^ERR-001$' is rewritten to the same equality.
Use a range for the fixed start. code >= 'ERR-' AND code < 'ERR.' is pushed down, so only the rows in the range reach the pattern test. Your range doesn't appear as a step in the plan; the two FILTER steps shown are the pattern's own, as above:

plan(f"SELECT id FROM {TABLE} WHERE code >= 'ERR-' AND code < 'ERR.' AND regexp_full_match(code, 'ERR-\\d{{3}}')")
run(f"SELECT id FROM {TABLE} WHERE code >= 'ERR-' AND code < 'ERR.' AND regexp_full_match(code, 'ERR-\\d{{3}}') "
    "ORDER BY id LIMIT 10")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  regexp_full_match(code,  │
│        'ERR-\d{3}')       │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ ((code >= 'ERR-000'::BLOB)│
│   AND (code <= 'ERR-999': │
│          :BLOB))          │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            code           │
│             id            │
│                           │
└───────────────────────────┘
[(1,)]

Validate at load time. If you check the form of a value on every query, check it once when you write the data and store the result in a BOOLEAN column.

Limitations and errors

No case-insensitive operator. ~* and !~* fail with Scalar Function with name ~* does not exist!. Use regexp_full_match(s, pattern, 'i'), or (?i) in the pattern.
~ binds more tightly than ||. 'abc' ~ 'a' || 'bc' is ('abc' ~ 'a') || 'bc', the string 'falsebc'. Put a built pattern in parentheses: 'abc' ~ ('a' || 'bc').
No ~ ANY or ~ ALL. s ~ ANY ([...]) fails with Unsupported comparison "~" for ANY/ALL subquery. Combine the tests with OR, or use one pattern with |.
~ on a number is bitwise NOT. ~ 5 is -6; see bitwise NOT. As a binary operator, ~ takes strings only.
Some pattern features aren't available, such as backreferences and lookahead: see syntax and options.

run("SELECT 'abc' ~* 'B'")
run("SELECT 'abc' ~ 'a' || 'bc', 'abc' ~ ('a' || 'bc')")
run("SELECT 'abc' ~ ANY (['a.*', 'x'])")
run("SELECT regexp_full_match('abc', '.*B.*', 'i'), 'abc' ~ 'x|a.*', ~ 5")

InternalError: Catalog Error: Scalar Function with name ~* does not exist!
[('falsebc', True)]
InternalError: Parse query: 'SELECT 'abc' ~ ANY (['a.*', 'x'])' failed with: Parser Error: Unsupported comparison "~" for ANY/ALL subquery
[(True, True, -6)]

Similar functions

Function	Tests	Result for `'abc'` and `a.c`
`regexp_full_match('abc', 'a.c')`, `~`	a regular expression, the whole string	`true`
`NOT ('abc' !~ 'a.c')`	the same, negated twice	`true`
SIMILAR TO `'a.c'`	a regular expression, the whole string	`true`
regexp_matches`('abc', '^a.c$')`	a regular expression, anywhere unless anchored	`true`
LIKE `'a_c'`	`%` and `_` wildcards, the whole string	`true`

run("SELECT regexp_full_match('abc', 'a.c'), 'abc' ~ 'a.c', NOT ('abc' !~ 'a.c'), 'abc' SIMILAR TO 'a.c', "
    "regexp_matches('abc', '^a.c$'), 'abc' LIKE 'a_c'")
run("SELECT regexp_full_match('xabcx', 'abc'), regexp_matches('xabcx', 'abc')")

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

Clean up:

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

[]
[]

regexp_full_match