--- title: "LIKE and NOT LIKE" slug: "dev-database-reference-sql-functions-string-like" updated: 2026-10-30T18:18:56Z published: 2026-10-30T18:18:56Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-string-like" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # LIKE and NOT LIKE *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Search and split](/develop/docs/dev-database-reference-sql-functions-string-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](/develop/docs/dev-database-reference-sql-functions-string-like-escape). ## Set up the examples The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-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](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), 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, "") 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](/develop/docs/dev-database-reference-sql-functions-string-glob) or [SIMILAR TO](/develop/docs/dev-database-reference-sql-functions-string-similar-to). - **To ignore case,** use [ILIKE](/develop/docs/dev-database-reference-sql-functions-string-ilike), or `COLLATE nocase` on either side: `'ABC' COLLATE nocase LIKE 'abc'` is true ([COLLATE](/develop/docs/dev-database-reference-sql-functions-string-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](/develop/docs/dev-database-reference-sql-functions-string-like#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](/develop/docs/dev-database-guides-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](/develop/docs/dev-database-reference-sql-functions-string-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](/develop/docs/dev-database-reference-sql-functions-string-ilike) `'A_C'` | the same as `LIKE` | ignored | `true` | | [GLOB](/develop/docs/dev-database-reference-sql-functions-string-glob) `'a?c'` | `*` any run, `?` one, `[...]` a set | sensitive | `true` | | [SIMILAR TO](/develop/docs/dev-database-reference-sql-functions-string-similar-to) `'a.c'` | a regular expression, whole string | sensitive | `true` | | [regexp_matches](/develop/docs/dev-database-reference-sql-functions-string-regexp-matches) `(s, '^a.c$')` | a regular expression, anywhere unless anchored | sensitive | `true` | | [like_escape](/develop/docs/dev-database-reference-sql-functions-string-like-escape) `(s, 'a_c', '$')` | `LIKE` with an escape character, as a function | sensitive | `true` | | [starts_with](/develop/docs/dev-database-reference-sql-functions-string-starts-with), [ends_with](/develop/docs/dev-database-reference-sql-functions-string-ends-with), [contains](/develop/docs/dev-database-reference-sql-functions-string-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() ``` ``` [] [] ``` ## See also - [Search and split](/develop/docs/dev-database-reference-sql-functions-string-search-and-split): all the search and pattern functions. - [Operators and predicates](/develop/docs/dev-database-reference-sql-functions-operators-and-predicates#pattern-matching). - [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns). - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.