--- title: "ILIKE and NOT ILIKE" slug: "dev-database-reference-sql-functions-string-ilike" updated: 2026-10-30T18:18:56Z published: 2026-10-30T18:18:56Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-string-ilike" --- > ## 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. # ILIKE and NOT ILIKE *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 ILIKE pattern` is [LIKE](/develop/docs/dev-database-reference-sql-functions-string-like) without regard to case: `%` stands for any run of characters, `_` for exactly one, and upper and lower case letters match each other. `NOT ILIKE` is the opposite. `~~*` and `!~~*` are the operator forms, and the names that plans and errors show. ## Syntax ``` s ILIKE pattern s ILIKE pattern ESCAPE escape_character s NOT ILIKE pattern s NOT ILIKE pattern ESCAPE escape_character s ~~* pattern s !~~* pattern ``` | Argument | Type | Description | | --- | --- | --- | | `s` | `VARCHAR` | The string to test. | | `pattern` | `VARCHAR` | The pattern, as for [LIKE](/develop/docs/dev-database-reference-sql-functions-string-like): `%` matches any run of characters, `_` exactly one. | | `escape_character` | `VARCHAR`, empty or one character | Optional. In the pattern, this character followed by `%`, `_` or itself matches that character literally. | **Returns:** `BOOLEAN`. **Aliases:** `~~*` (`ILIKE`), `!~~*` (`NOT ILIKE`). The function forms with an escape character are [ilike_escape and not_ilike_escape](/develop/docs/dev-database-reference-sql-functions-string-ilike-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_ilike`. 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 four products whose names use different cases. Row 3 has an accented capital. 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_ilike"' 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}.products" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, name VARCHAR)") run(f"INSERT INTO {TABLE} VALUES " "(1, 'USB-C Cable'), " "(2, 'usb hub'), " "(3, '\u00c9COLE notebook'), " "(4, NULL)") ``` ``` [] [] [(4,)] ``` ## What it does `ILIKE` uses the same pattern syntax as [LIKE](/develop/docs/dev-database-reference-sql-functions-string-like), the wildcards `%` and `_` and an optional `ESCAPE`, and compares letters without regard to case: - **Accented and non-Latin letters match their other case.** `'ÉCOLE' ILIKE 'école'`, `'Ä' ILIKE 'ä'` and `'Σ' ILIKE 'σ'` are true. - **A letter matches one letter of the other case, not a spelling.** `'STRASSE' ILIKE 'straße'` is false, while the capital sharp s `'ẞ' ILIKE 'ß'` is true. - **Some letters have more than one lower-case form.** `'ς' ILIKE 'Σ'` (final sigma) and `'I' ILIKE 'ı'` (dotless i) are false; `'İ' ILIKE 'i'` is true. - **Accents still count.** `ILIKE` ignores case, not accents: a precomposed `É` doesn't match `e` followed by a combining accent (U+0301). To ignore accents, `COLLATE noaccent` works with `LIKE`: `'café' COLLATE noaccent LIKE 'cafe'` is true ([COLLATE](/develop/docs/dev-database-reference-sql-functions-string-collate)). `s NOT ILIKE p` returns the opposite of `s ILIKE p`, and `NULL` when that is `NULL`. ## NULLs and edge cases | Input | Result | | --- | --- | | `NULL ILIKE 'h%'`, `'abc' ILIKE NULL`, `NULL NOT ILIKE 'h%'` | `NULL` | | `'' ILIKE ''`, `'' ILIKE '%'` | `true` | | `'abc' ILIKE 'ABC'`, `'ABC' ILIKE 'a_c'` | `true` | | `'ÉCOLE' ILIKE 'école'`, `'Ä' ILIKE 'ä'` | `true` | | `'STRASSE' ILIKE 'straße'` | `false` | | `'ẞ' ILIKE 'ß'` | `true` | | `'Σ' ILIKE 'σ'`, `'ς' ILIKE 'Σ'` | `true`, `false` | | `'İ' ILIKE 'i'`, `'I' ILIKE 'ı'` | `true`, `false` | | `'É' ILIKE 'e\u0301'` (precomposed and decomposed) | `false` | | `'A%B' ILIKE 'a$%b' ESCAPE '$'`, `'AxB' ILIKE 'a$%b' ESCAPE '$'` | `true`, `false` | | `'ABC' NOT ILIKE 'a%'` | `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-ilike#set-up-the-examples): ``` run("SELECT NULL ILIKE 'h%', 'abc' ILIKE NULL, NULL NOT ILIKE 'h%', '' ILIKE '', '' ILIKE '%', " "'abc' ILIKE 'ABC', 'ABC' ILIKE 'a_c'") run("SELECT '\u00c9COLE' ILIKE '\u00e9cole', '\u00c4' ILIKE '\u00e4', 'STRASSE' ILIKE 'stra\u00dfe', '\u1e9e' ILIKE '\u00df'") run("SELECT '\u03a3' ILIKE '\u03c3', '\u03c2' ILIKE '\u03a3', '\u0130' ILIKE 'i', 'I' ILIKE '\u0131', '\u00c9' ILIKE 'e\u0301'") run("SELECT 'A%B' ILIKE 'a$%b' ESCAPE '$', 'AxB' ILIKE 'a$%b' ESCAPE '$', 'ABC' NOT ILIKE 'a%'") run("SELECT 123 ILIKE 'h%'") ``` ``` [(None, None, None, True, True, True, True)] [(True, True, False, True)] [(True, False, True, False, False)] [(True, 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 Find the USB products whatever their case, and compare with `LIKE`. The row with no name gives `NULL`: ``` run(f"SELECT id, name ILIKE 'usb%', name LIKE 'usb%', name NOT ILIKE '%cable' FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, True, False, False), (2, True, True, True), (3, False, False, True), (4, None, None, None)] ``` Keep the rows whose name starts with `école` in any case: ``` run(f"SELECT id, name FROM {TABLE} WHERE name ILIKE '\u00e9cole%' ORDER BY id LIMIT 10") ``` ``` [(3, 'ÉCOLE notebook')] ``` The operator forms give the same results: ``` run(f"SELECT id, name ~~* 'USB%', name !~~* 'USB%' FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, True, False), (2, True, False), (3, False, True), (4, 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`). VAST DataBase can evaluate a comparison of a column with a constant, but not a case-insensitive match, so `ILIKE` is always computed by the Query Engine: - **In the `SELECT` list,** the scan reads the column and the Query Engine evaluates `ILIKE` for 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. No rows are skipped in storage. Unlike `LIKE`, `ILIKE` isn't rewritten: a prefix pattern stays `s ~~* 'ab%'`, and a pattern without wildcards isn't turned into an equality test, so it isn't pushed down. `NOT ILIKE` appears as `!~~*`. The plan of the `WHERE` example shows the `FILTER` step above the scan: ``` plan(f"SELECT id FROM {TABLE} WHERE name ILIKE 'usb%'") plan(f"SELECT id FROM {TABLE} WHERE name ILIKE 'usb hub'") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (name ~~* 'usb%') │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ name │ │ id │ │ │ └───────────────────────────┘ ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (name ~~* 'usb hub') │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ name │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Store a lower-case copy of the column.** Write `lower(name)` to its own column when you load the rows, and lower-case the constant. On that column, `name_lower = 'usb hub'` is pushed down to VAST DataBase, a range such as `name_lower >= 'usb' AND name_lower < 'usc'` is pushed down too, and `name_lower LIKE 'usb%'` becomes `prefix()`. `lower(name) = 'usb hub'` and `name COLLATE nocase = 'usb hub'` are evaluated in the Query Engine. The plans: ``` plan(f"SELECT id FROM {TABLE} WHERE name = 'usb hub'") plan(f"SELECT id FROM {TABLE} WHERE lower(name) = 'usb hub'") ``` ``` ┌───────────────────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: id │ │ │ └───────────────────────────┘ ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (lower(name) = 'usb hub') │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ name │ │ id │ │ │ └───────────────────────────┘ ``` - **Combine it with a pushed-down condition.** In `id > 1 AND name ILIKE 'usb%'`, `id > 1` is still evaluated in VAST DataBase, so `ILIKE` runs only on the rows that pass it. ## 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 ILIKE`). Cast the value to `VARCHAR` first. - **The escape character is empty or one character.** A longer one fails with `Invalid escape string. Escape string must be empty or one character.`; see [ilike_escape](/develop/docs/dev-database-reference-sql-functions-string-ilike-escape#limitations-and-errors). - **No `ILIKE ANY`.** `s ILIKE ANY (['x%', 'a%'])` fails with `Unsupported comparison "~~*" for ANY/ALL subquery`. Combine the tests with `OR`. - **No `ilike()` function.** `ilike('abc', 'A%')` fails with `Scalar Function with name ilike does not exist!`. Use the operator, or [ilike_escape](/develop/docs/dev-database-reference-sql-functions-string-ilike-escape). ``` run("SELECT 'abc' ILIKE ANY (['X%', 'A%'])") run("SELECT ilike('abc', 'A%')") run("SELECT 'abc' ILIKE 'X%' OR 'abc' ILIKE 'A%'") ``` ``` InternalError: Parse query: 'SELECT 'abc' ILIKE ANY (['X%', 'A%'])' failed with: Parser Error: Unsupported comparison "~~*" for ANY/ALL subquery InternalError: Catalog Error: Scalar Function with name ilike does not exist! [(True,)] ``` ## Similar functions | Function | Ignores | Result for `'Hello'` and `h` | | --- | --- | --- | | `ILIKE '%h%'` | case | `true` | | [LIKE](/develop/docs/dev-database-reference-sql-functions-string-like) `'%h%'` | nothing | `false` | | `s COLLATE nocase LIKE '%h%'` | case ([COLLATE](/develop/docs/dev-database-reference-sql-functions-string-collate)) | `true` | | [contains](/develop/docs/dev-database-reference-sql-functions-string-contains)`(lower(s), 'h')` | case, after [lower](/develop/docs/dev-database-reference-sql-functions-string-lower) | `true` | | [regexp_matches](/develop/docs/dev-database-reference-sql-functions-string-regexp-matches)`(s, 'h', 'i')` | case (the `i` option) | `true` | | [ilike_escape](/develop/docs/dev-database-reference-sql-functions-string-ilike-escape) | case; the escape character as an argument | | ``` run("SELECT 'Hello' ILIKE '%h%', 'Hello' LIKE '%h%', 'Hello' COLLATE nocase LIKE '%h%', " "contains(lower('Hello'), 'h'), regexp_matches('Hello', 'h', 'i')") ``` ``` [(True, False, True, True, True)] ``` 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.