ILIKE and NOT ILIKE

Applies to VAST Cluster 5.5.

Functions › Search and split › Pattern matching. s ILIKE pattern is 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: `%` 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.

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_ilike. 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 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, "<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, 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).

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:

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.
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.

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 `'%h%'`	nothing	`false`
`s COLLATE nocase LIKE '%h%'`	case (COLLATE)	`true`
contains`(lower(s), 'h')`	case, after lower	`true`
regexp_matches`(s, 'h', 'i')`	case (the `i` option)	`true`
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()

[]
[]