SIMILAR TO

Applies to VAST Cluster 5.5.

Functions › Regular expressions › Pattern matching. s SIMILAR TO pattern is true if the whole string matches a regular expression. It's the operator form of regexp_full_match, and s NOT SIMILAR TO pattern returns the opposite. The pattern is a regular expression, not a LIKE pattern: . and .* are the wildcards, and % and _ are ordinary characters.

Syntax

s SIMILAR TO pattern
s NOT SIMILAR TO pattern

Returns: BOOLEAN.

Related: s SIMILAR TO pattern runs as regexp_full_match(s, pattern), and s NOT SIMILAR TO pattern as NOT regexp_full_match(s, pattern). There's no similar_to() function.

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_similar_to. 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 log lines: a level, a date and a message. Row 3 has its level in lower case. 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_similar_to"'

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, 'INFO 2026-10-16 job started'), "
    "(3, 'error 2026-10-17 timeout'), "
    "(4, NULL)")

[]
[]
[(4,)]

What it does

SIMILAR TO tests whether the regular expression matches the whole string, as if it started with ^ and ended with $. To match anywhere in the string, put .* at both ends, or use regexp_matches.

• Case counts. 'ABC' SIMILAR TO 'abc' is false. Start the pattern with (?i) to ignore case: 'ABC' SIMILAR TO '(?i)abc' is true.
• . doesn't match a newline. 'a\nb' SIMILAR TO 'a.b' is false. Start the pattern with (?s) to let . match one.
• . matches one character outside ASCII, such as a precomposed é.
• The pattern can be a column. Each row is tested against its own pattern.

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 NULL SIMILAR TO 'a', 'abc' SIMILAR TO NULL, '' SIMILAR TO '', '' SIMILAR TO '.*', "
    "'\u00e9' SIMILAR TO '.', 'a\nb' SIMILAR TO 'a.b', 'a\nb' SIMILAR TO '(?s)a.b'")
run("SELECT 'abc' SIMILAR TO 'abc', 'abc' SIMILAR TO 'b', 'abc' SIMILAR TO '.*b.*', 'abc' SIMILAR TO 'a.c'")
run("SELECT 'abc' SIMILAR TO '(a|x)bc', 'abc' SIMILAR TO '[a-c]+', 'abc' SIMILAR TO 'a%', 'abc' SIMILAR TO 'a_c', "
    "'a_c' SIMILAR TO 'a_c'")
run("SELECT 'ABC' SIMILAR TO 'abc', 'ABC' SIMILAR TO '(?i)abc'")
run("SELECT 'abc' NOT SIMILAR TO NULL, 'abc' NOT SIMILAR TO 'a.c', 'abc' NOT SIMILAR TO 'b'")
run("SELECT 'abc' SIMILAR TO '('")
run("SELECT 'abc' SIMILAR TO 'a.*' ESCAPE '$'")
run("SELECT 123 SIMILAR TO '1.*'")

[(None, None, True, True, True, False, True)]
[(True, False, True, True)]
[(True, True, False, False, True)]
[(False, True)]
[(None, False, True)]
InternalError: Invalid Input Error: missing ): (
InternalError: Not implemented Error: Custom escape in SIMILAR TO
InternalError: Binder Error: No function matches the given name and argument types 'regexp_full_match(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.

Examples

Which lines are errors, case-sensitively and ignoring case, and which have the shape level, date, message. In Python, '\\d' is the SQL text \d:

run(f"SELECT id, line SIMILAR TO 'ERROR .*', line SIMILAR TO '(?i)error .*', "
    f"line SIMILAR TO '[A-Za-z]+ \\d+-\\d+-\\d+ .*' FROM {TABLE} ORDER BY id LIMIT 10")

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

Keep the lines that aren't errors in any case:

run(f"SELECT id, line FROM {TABLE} WHERE line NOT SIMILAR TO '(?i)error .*' ORDER BY id LIMIT 10")

[(2, 'INFO 2026-10-16 job started')]

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 SIMILAR TO is always computed by the Query Engine, as regexp_full_match:

• In the SELECT list, the scan reads the column and the Query Engine tests each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine keeps the rows that match.
• A pattern with a fixed beginning, such as 'ERROR .*', gets a second FILTER step below the pattern test: a range test on the column, which skips the pattern test for rows that can't match. It runs in the Query Engine too, not in the scan.

The plan of a pattern with a fixed beginning shows both FILTER steps:

plan(f"SELECT id FROM {TABLE} WHERE line SIMILAR TO 'ERROR .*'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  regexp_full_match(line,  │
│        'ERROR .*')        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ ((line >= 'ERROR '::BLOB) │
│  AND (line <= 'ERROR \xF4 │
│   \xBF\xBF\xC0'::BLOB))   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            line           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Use a plain comparison when you can. A range on the raw column, such as line >= 'ERROR ' AND line < 'ERROR!', is pushed down to VAST DataBase, so rows that don't match never leave storage; no regular expression is. The plan of the range has no FILTER step.
• Store the part you filter on. If you often filter on the level, store it in its own column when you write the rows, and compare that column with a constant.
• Combine it with a pushed-down condition. In id > 1 AND line SIMILAR TO '(?i)error .*', id > 1 is still evaluated in VAST DataBase, so the pattern runs only on the rows that pass it.

plan(f"SELECT id FROM {TABLE} WHERE line >= 'ERROR ' AND line < 'ERROR!'")
run(f"SELECT id FROM {TABLE} WHERE line >= 'ERROR ' AND line < 'ERROR!' ORDER BY id LIMIT 10")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
[(1,)]

Limitations and errors

• Regular expressions only. % and _ aren't wildcards in SIMILAR TO; use LIKE for those.
• No ESCAPE. SIMILAR TO ... ESCAPE fails with Not implemented Error: Custom escape in SIMILAR TO. Escape a special character in the pattern with a backslash instead, such as 'a\.c'.
• An invalid pattern fails. A pattern such as '(' fails with Invalid Input Error: missing ): (. See regular expression syntax for what a pattern can contain.
• VARCHAR only. A number, BLOB or list fails with No function matches the given name and argument types 'regexp_full_match(INTEGER_LITERAL, STRING_LITERAL)' (with the type you passed). Cast it to VARCHAR first.
• No similar_to() function. It fails with Scalar Function with name similar_to does not exist!. Use SIMILAR TO or regexp_full_match.

run("SELECT 'a.c' SIMILAR TO 'a\\.c', 'abc' SIMILAR TO 'a\\.c'")
run("SELECT CAST(123 AS VARCHAR) SIMILAR TO '1.*'")
run("SELECT similar_to('abc', 'a.c')")

[(True, False)]
[(True,)]
InternalError: Catalog Error: Scalar Function with name similar_to does not exist!

Similar functions

run("SELECT 'xabcx' SIMILAR TO 'abc', regexp_full_match('xabcx', 'abc'), regexp_matches('xabcx', 'abc'), "
    "'xabcx' LIKE '%abc%', contains('xabcx', 'abc')")
run("SELECT 'ABC' SIMILAR TO '(?i)a.*', regexp_full_match('ABC', 'a.*', 'i')")

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

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.
• regexp_full_match, regexp_matches and regular expression syntax.
• Operators and predicates.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.