regexp_escape

Applies to VAST Cluster 5.5.

Functions › Regular expressions › Regular expressions. Puts a backslash in front of every character of a string that could have a meaning in a regular expression, so the result matches the original text literally. Use it to build a pattern from a search term, a column value or user input.

Syntax

regexp_escape(s)

Returns: VARCHAR.

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_escape. 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 search terms and the text to search. 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_escape"'

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}.terms"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, term VARCHAR, body VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'a.b', 'see axb'), "
    "(2, '1+1', '1+1=2'), "
    "(3, 'price (USD)', 'Total price (USD): 42'), "
    "(4, '50%', 'up 50% today'), "
    "(5, NULL, NULL)")

[]
[]
[(5,)]

What it does

In a regular expression, characters such as ., +, ( and * have a meaning: a.b matches axb as well as a.b. regexp_escape puts a backslash in front of each of them, so regexp_escape('a.b') returns a\.b, which matches only the text a.b.

It escapes every ASCII character that isn't a letter, a digit or an underscore, including the space, %, -, #, :, /, a newline and a tab. Letters, digits, _ and non-ASCII characters (such as é or an emoji) are returned unchanged. The escaped string is a pattern that matches the original string: regexp_full_match(s, regexp_escape(s)) is true.

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_escape(NULL), regexp_escape(''), regexp_escape('a b'), regexp_escape('a_b%c'), "
    "regexp_escape('\u00e9'), regexp_escape('\U0001F600'), regexp_escape('a#b'), regexp_escape('a:b')")
run("SELECT regexp_escape('a\nb'), regexp_escape('a\tb')")
run("SELECT regexp_escape('.^$*+?()[]{}|-/')")
run("SELECT regexp_escape(123)")

[(None, '', 'a\\ b', 'a_b\\%c', 'é', '😀', 'a\\#b', 'a\\:b')]
[('a\\\nb', 'a\\\tb')]
[('\\.\\^\\$\\*\\+\\?\\(\\)\\[\\]\\{\\}\\|\\-\\/',)]
InternalError: Binder Error: No function matches the given name and argument types 'regexp_escape(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

Each term, escaped:

run(f"SELECT id, term, regexp_escape(term) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'a.b', 'a\\.b'), (2, '1+1', '1\\+1'), (3, 'price (USD)', 'price\\ \\(USD\\)'), (4, '50%', '50\\%'), (5, None, None)]

Search each row's text for its term, first as a pattern and then escaped. Used as a pattern, each term's ., +, ( and ) keep their meaning; escaped, each term matches only its own text:

run(f"SELECT id, regexp_matches(body, term), regexp_matches(body, regexp_escape(term)) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

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

Use the escaped text inside a larger pattern. Here it replaces the literal text 1+1:

run("SELECT regexp_replace('1+1=2', regexp_escape('1+1'), 'two'), "
    "regexp_full_match('a.b*(c)', regexp_escape('a.b*(c)'))")

[('two=2', True)]

Keep the rows whose text contains (USD):

run(f"SELECT id, body FROM {TABLE} WHERE regexp_matches(body, regexp_escape('(USD)')) ORDER BY id LIMIT 10")

[(3, 'Total price (USD): 42')]

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). A regular-expression match is a function of a column, so it's computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes regexp_escape() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine runs the match and keeps the rows that pass.

The plan of the WHERE example shows the FILTER step above the scan. The argument is a constant, so the escaped text is worked out when the query is planned, and the match is rewritten into a contains() test, as for a plain literal pattern (regexp_matches):

plan(f"SELECT id FROM {TABLE} WHERE regexp_matches(body, regexp_escape('(USD)'))")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  contains(body, '(USD)')  │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            body           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• For a fixed search text, don't use a pattern at all. contains(body, '(USD)') finds literal text without a regular expression, and option 'l' makes regexp_matches treat its whole pattern as literal text. Use regexp_escape when only part of the pattern is literal, or when the pattern comes from a column.
• Combine it with a pushed-down condition. In id > 100 AND regexp_matches(...), the comparison of the raw column with a constant can be evaluated in VAST DataBase, so the match runs only on the rows that pass it. See pushdowns.

Limitations and errors

• VARCHAR only. A number, a date, a boolean, a BLOB or a list fails with No function matches the given name and argument types, for example 'regexp_escape(INTEGER_LITERAL)'. Cast the value to VARCHAR first.
• It escapes for regular expressions, not for LIKE. _ is left as it is. To match a literal % or _ with LIKE, see like_escape.

Similar functions

run("SELECT regexp_matches('axb', regexp_escape('a.b')), regexp_matches('axb', 'a.b', 'l'), "
    "regexp_matches('axb', 'a.b'), contains('axb', 'a.b')")

[(False, False, True, 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.
• Regular expression syntax.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.