LIKE and NOT LIKE

Applies to VAST Cluster 5.5.

Functions › Search and split › Pattern matching. s LIKE pattern is true if the whole string matches a pattern in which % stands for any run of characters and _ for exactly one. NOT LIKE is the opposite. ESCAPE names a character that makes the next % or _ literal. ~~ and !~~ are the operator forms, and the names that plans and errors show.

Syntax

s LIKE pattern
s LIKE pattern ESCAPE escape_character
s NOT LIKE pattern
s NOT LIKE pattern ESCAPE escape_character
s ~~ pattern
s !~~ pattern

Argument	Type	Description
`s`	`VARCHAR`	The string to test.
`pattern`	`VARCHAR`	The pattern. `%` matches any run of characters, including none; `_` matches exactly one character. Every other character matches itself.
`escape_character`	`VARCHAR`, empty or one character	Optional. In the pattern, this character followed by `%`, `_` or itself matches that character literally.

Returns: BOOLEAN.

Aliases: ~~ (LIKE), !~~ (NOT LIKE). The function forms with an escape character are like_escape and not_like_escape.

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_like. 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 file paths. Row 3 has a literal _ and % in its name; row 4 is almost the same name without them. 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_like"'

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, '/logs/app/2026-10-13.log'), "
    "(2, '/logs/db/2026-10-12.log'), "
    "(3, '/tmp/load_50%.csv'), "
    "(4, '/tmp/loadx50.csv'), "
    "(5, NULL)")

[]
[]
[(5,)]

What it does

The pattern must match the whole string. 'xabcx' LIKE 'abc' is false; 'xabcx' LIKE '%abc%' is true.

In the pattern	Matches	Example	Result
`%`	any run of characters, including none	`'abc' LIKE 'a%c%'`	`true`
`_`	exactly one character (code point)	`'abc' LIKE 'a_c'`	`true`
any other character	itself, case-sensitive	`'abc' LIKE 'ABC'`	`false`
escape character + `%`, `_` or itself	that character	`'a%b' LIKE 'a$%b' ESCAPE '$'`	`true`

_ is one code point. A precomposed é matches _; e followed by a combining accent (U+0301) needs __, and so does a flag emoji. Both wildcards match a line break.
There's no default escape character. A backslash is an ordinary character: 'a%b' LIKE 'a\%b' is false, and 'a\%b' LIKE 'a\%b' is true. Add ESCAPE '\' to make it an escape.
An escape character before any other character stands for that character: 'ab' LIKE 'a$b' ESCAPE '$' is true. An empty ESCAPE '' means no escape character.
[...] and * are ordinary characters. For character classes, use GLOB or SIMILAR TO.
To ignore case, use ILIKE, or COLLATE nocase on either side: 'ABC' COLLATE nocase LIKE 'abc' is true (COLLATE).

s NOT LIKE p returns the opposite of s LIKE p, and NULL when that is NULL.

NULLs and edge cases

Input	Result
`NULL LIKE '%'`, `'abc' LIKE NULL`, `NULL NOT LIKE '%'`	`NULL`
`'abc' LIKE 'ab' ESCAPE NULL`	`NULL`
`'' LIKE '%'`, `'' LIKE ''`	`true`
`'abc' LIKE ''`, `'' LIKE '_'`	`false`
`'abc ' LIKE '%c'` (trailing space)	`false`
`'é' LIKE '_'` (precomposed)	`true`
`'e\u0301' LIKE '_'`, `'e\u0301' LIKE '__'`	`false`, `true`
`'🇫🇷' LIKE '__'` (a flag is two code points)	`true`
A line break: `'a` + line break + `b' LIKE 'a_b'`	`true`
`'a%b' LIKE 'a\%b'` (no `ESCAPE`)	`false`
`'a%b' LIKE 'a\%b' ESCAPE '\'`	`true`
`'a$b' LIKE 'a$$b' ESCAPE '$'`	`true`
`'ab' LIKE 'ab$' ESCAPE '$'` (pattern ends with the escape character)	`false`
`'a' LIKE '[a]'`, `'ab' LIKE 'a*'`	`false`
`'abc' NOT LIKE '%'`	`false`
A number, date, boolean, `BLOB` or list	Error: `No function matches the given name and argument types '~~(INTEGER_LITERAL, STRING_LITERAL)'` (for a number)

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

run("SELECT NULL LIKE '%', 'abc' LIKE NULL, NULL NOT LIKE '%', 'abc' LIKE 'ab' ESCAPE NULL")
run("SELECT '' LIKE '%', '' LIKE '', 'abc' LIKE '', '' LIKE '_', 'abc ' LIKE '%c'")
run("SELECT '\u00e9' LIKE '_', 'e\u0301' LIKE '_', 'e\u0301' LIKE '__', '\U0001F1EB\U0001F1F7' LIKE '__', 'a\nb' LIKE 'a_b'")
run("SELECT 'a%b' LIKE 'a\\%b', 'a%b' LIKE 'a\\%b' ESCAPE '\\', 'a$b' LIKE 'a$$b' ESCAPE '$', "
    "'ab' LIKE 'ab$' ESCAPE '$'")
run("SELECT 'a' LIKE '[a]', 'ab' LIKE 'a*', 'abc' NOT LIKE '%'")
run("SELECT 123 LIKE '1%'")

[(None, None, None, None)]
[(True, True, False, False, False)]
[(True, False, True, True, True)]
[(False, True, True, False)]
[(False, False, False)]
InternalError: Binder Error: No function matches the given name and argument types '~~(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.

Examples

Test each path against a prefix, a suffix, a pattern with _, and the opposite of the prefix. The row with no path gives NULL:

run(f"SELECT id, path LIKE '/logs/%', path LIKE '%.log', path LIKE '/logs/__/%', path NOT LIKE '/logs/%' "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

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

Without an escape character, _ in '%load_50%' matches any character, so rows 3 and 4 both match. With ESCAPE '$', $_ and $% are literal, and only row 3 matches:

run(f"SELECT id, path FROM {TABLE} WHERE path LIKE '%load_50%' ORDER BY id LIMIT 10")
run(f"SELECT id, path FROM {TABLE} WHERE path LIKE '%load$_50$%%' ESCAPE '$' ORDER BY id LIMIT 10")

[(3, '/tmp/load_50%.csv'), (4, '/tmp/loadx50.csv')]
[(3, '/tmp/load_50%.csv')]

The operator forms give the same results:

run(f"SELECT id, path ~~ '/tmp/%', path !~~ '/tmp/%' FROM {TABLE} ORDER BY id LIMIT 10")

[(1, False, True), (2, False, True), (3, True, False), (4, True, False), (5, 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). Where a LIKE condition in WHERE runs depends on the pattern. The Query Engine rewrites simple patterns before it plans the query:

Pattern	Becomes	Runs in
no wildcard: `LIKE 'abc'`	`= 'abc'`	VAST DataBase (pushed down; no `FILTER` step)
`LIKE 'ab%'`	`prefix(s, 'ab')`	a `FILTER` step in the Query Engine
`LIKE '%ab'`	`suffix(s, 'ab')`	a `FILTER` step
`LIKE '%ab%'`	`contains(s, 'ab')`	a `FILTER` step
`NOT LIKE 'ab%'`	`NOT prefix(s, 'ab')`	a `FILTER` step
anything else, such as `_` or a `%` in the middle	`s ~~ pattern`	a `FILTER` step
with `ESCAPE`	`like_escape(s, pattern, escape)`	a `FILTER` step
a pattern from a column	`s ~~ column`	a `FILTER` step

In a FILTER step, the scan sends the column for every row and the Query Engine keeps the rows that match; no rows are skipped in storage. The plans of a whole-value pattern, a prefix and a pattern with _:

plan(f"SELECT id FROM {TABLE} WHERE path LIKE '/tmp/loadx50.csv'")
plan(f"SELECT id FROM {TABLE} WHERE path LIKE '/logs/%'")
plan(f"SELECT id FROM {TABLE} WHERE path LIKE '/logs/__/%'")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│   prefix(path, '/logs/')  │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            path           │
│             id            │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│   (path ~~ '/logs/__/%')  │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            path           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

Use a range instead of a prefix pattern. path >= '/logs/' AND path < '/logs0' selects the same rows as LIKE '/logs/%', and it's pushed down to VAST DataBase. The upper bound is the prefix with its last character replaced by the next one (/ is followed by 0). For several prefixes, OR the ranges: an OR of ranges on one column is pushed down too, an OR of LIKE prefixes isn't. The plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE path >= '/logs/' AND path < '/logs0'")
run(f"SELECT id FROM {TABLE} WHERE path >= '/logs/' AND path < '/logs0' ORDER BY id LIMIT 10")

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

A pattern without wildcards is an equality test, and is pushed down.
Combine it with a pushed-down condition. In id > 1 AND path LIKE '/logs/%', id > 1 is still evaluated in VAST DataBase, and only prefix() is in the FILTER step.
Sort the table on the searched column. A range on a sorted column lets the scan skip whole blocks; see indexing and data skipping.

Limitations and errors

Strings only. A number, a date, a boolean, a BLOB or a list fails with No function matches the given name and argument types '~~(...)' ('!~~(...)' for NOT LIKE). Cast the value to VARCHAR first.
The escape character is empty or one character. ESCAPE '$$' fails with Invalid escape string. Escape string must be empty or one character.
A pattern can't end with the escape character when it is %. 'ab' LIKE 'a%' ESCAPE '%' fails with Like pattern must not end with escape character!. With another escape character, 'ab' LIKE 'ab$' ESCAPE '$' is false and doesn't fail.
No LIKE ANY or LIKE ALL. s LIKE ANY (['a%', 'b%']) fails with Unsupported comparison "~~" for ANY/ALL subquery. Combine the tests with OR or AND.
No like() function. like('abc', 'a%') fails with Scalar Function with name like does not exist!, and ~~(...) can't be called as a function. Use the operator, or like_escape.

run("SELECT 'ab' LIKE 'ab' ESCAPE '$$'")
run("SELECT 'ab' LIKE 'a%' ESCAPE '%'")
run("SELECT 'abc' LIKE ANY (['x%', 'a%'])")
run("SELECT 'abc' LIKE 'x%' OR 'abc' LIKE 'a%', CAST(123 AS VARCHAR) LIKE '1%'")

OSError: Invalid escape string. Escape string must be empty or one character.
OSError: Like pattern must not end with escape character!
InternalError: Parse query: 'SELECT 'abc' LIKE ANY (['x%', 'a%'])' failed with: Parser Error: Unsupported comparison "~~" for ANY/ALL subquery
[(True, True)]

Similar functions

Function	Wildcards	Case	Result for `'abc'`
`LIKE 'a_c'`	`%` any run, `_` one character	sensitive	`true`
ILIKE `'A_C'`	the same as `LIKE`	ignored	`true`
GLOB `'a?c'`	`*` any run, `?` one, `[...]` a set	sensitive	`true`
SIMILAR TO `'a.c'`	a regular expression, whole string	sensitive	`true`
regexp_matches `(s, '^a.c$')`	a regular expression, anywhere unless anchored	sensitive	`true`
like_escape `(s, 'a_c', '$')`	`LIKE` with an escape character, as a function	sensitive	`true`
starts_with, ends_with, contains	none: plain text	sensitive

run("SELECT 'abc' LIKE 'a_c', 'abc' ILIKE 'A_C', 'abc' GLOB 'a?c', 'abc' SIMILAR TO 'a.c', "
    "regexp_matches('abc', '^a.c$'), like_escape('abc', 'a_c', '$')")
run("SELECT 'ABC' LIKE 'a%', 'ABC' ILIKE 'a%', 'ABC' GLOB 'a*'")

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

Clean up:

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

[]
[]