ilike_escape

Applies to VAST Cluster 5.5.

Functions › Search and split › Pattern matching. ilike_escape(s, pattern, escape) is the function form of s ILIKE pattern ESCAPE escape: true if the whole string matches a LIKE pattern, ignoring case, where the escape character makes the next % or _ literal. not_ilike_escape returns the opposite, like NOT ILIKE ... ESCAPE.

Syntax

ilike_escape(s, pattern, escape)
not_ilike_escape(s, pattern, escape)

Returns: BOOLEAN.

Related: s ILIKE pattern ESCAPE escape and s NOT ILIKE pattern ESCAPE escape give the same results; for a case-sensitive match, use 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_ilike_escape. 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 in mixed case. Rows 1 and 3 have a literal _ or % in their names; rows 2 and 4 are the same names with another character in that place. 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_escape"'

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, '/Data/Run_1.TXT'), "
    "(2, '/data/runx1.txt'), "
    "(3, '/TMP/Load_50%.csv'), "
    "(4, '/tmp/load_50x.CSV'), "
    "(5, NULL)")

[]
[]
[(5,)]

What it does

ilike_escape(s, pattern, escape) gives the same result as s ILIKE pattern ESCAPE escape, and not_ilike_escape the same as s NOT ILIKE pattern ESCAPE escape. The pattern works as for like_escape, but upper and lower case match each other:

• The match covers the whole string, as for ILIKE. Put % at both ends to match anywhere in it.
• An empty escape means no escape character: % and _ are always wildcards.
• Choose an escape character that isn't in your data, such as $ or !. A backslash works too: ilike_escape('a_b', 'a\_b', '\') is true.
• _ matches one code point. A precomposed é matches _; e followed by a combining accent is two code points and doesn't.

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 ilike_escape(NULL, 'a', '$'), ilike_escape('a', NULL, '$'), ilike_escape('a', 'a', NULL), "
    "ilike_escape('', '', '$'), ilike_escape('', '%', '$'), ilike_escape('ab', 'ab', '')")
run("SELECT ilike_escape('axb', 'a$%b', '$'), ilike_escape('axb', 'a$_b', '$'), ilike_escape('a', 'a$', '$'), "
    "ilike_escape('a_b', 'a\\_b', '\\'), ilike_escape('a%', 'a%%', '%')")
run("SELECT ilike_escape('\u00e9', '_', '$'), ilike_escape('e\u0301', '_', '$'), ilike_escape('a\nb', 'a_b', '$')")
run("SELECT not_ilike_escape('a%b', 'a$%b', '$'), not_ilike_escape('AB', 'ab', '$'), not_ilike_escape('axb', 'a$%b', '$')")
run("SELECT ilike_escape('ab', 'a_', '$$')")
run("SELECT ilike_escape('ab', 'ab', '\u00e9')")

[(None, None, None, True, True, True)]
[(False, False, False, True, True)]
[(True, False, True)]
[(False, False, True)]
OSError: Invalid escape string. Escape string must be empty or one character.
OSError: Invalid escape string. Escape string must be empty or one character.

Examples

Without escaping, _ is a wildcard, so ILIKE '%run_1%' matches rows 1 and 2. With $ as the escape character, only the literal _ matches, in any case. The ILIKE ... ESCAPE form gives the same result:

run(f"SELECT id, path ILIKE '%run_1%', ilike_escape(path, '%run$_1%', '$'), path ILIKE '%run$_1%' ESCAPE '$' "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

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

Keep the files under /tmp/, in any case, whose name has a literal %, and those that don't:

run(f"SELECT id, path FROM {TABLE} WHERE ilike_escape(path, '/tmp/%$%%', '$') ORDER BY id LIMIT 10")
run(f"SELECT id, path FROM {TABLE} WHERE not_ilike_escape(path, '/tmp/%$%%', '$') ORDER BY id LIMIT 10")

[(3, '/TMP/Load_50%.csv')]
[(1, '/Data/Run_1.TXT'), (2, '/data/runx1.txt'), (4, '/tmp/load_50x.CSV')]

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 pattern match, so ilike_escape() is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes ilike_escape() 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.

A prefix pattern isn't rewritten to a cheaper test: ilike_escape(path, '/tmp/%', '$') stays ilike_escape in the plan. The plan of the WHERE example:

plan(f"SELECT id FROM {TABLE} WHERE ilike_escape(path, '/tmp/%$%%', '$')")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ ilike_escape(path, '/tmp/%│
│         $%%', '$')        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            path           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Store the case you search on. If you often match without regard to case, store a lower-case copy of the column when you write the rows. A comparison of that column with a lower-case constant, such as path_lower = '/tmp/load_50%.csv', is pushed down to VAST DataBase, so rows that don't match never leave storage.
• Use contains() for a literal substring. To find a literal % or _ anywhere, contains(path, '%') needs no escape character at all; %, _ and letters have no special meaning there, and case counts.
• Combine it with a pushed-down condition. In id > 2 AND ilike_escape(path, '%$%%', '$'), id > 2 is still evaluated in VAST DataBase, so ilike_escape() runs only on the rows that pass it.

run(f"SELECT id, contains(path, '%'), ilike_escape(path, '%$%%', '$') FROM {TABLE} ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE id > 2 AND ilike_escape(path, '%$%%', '$')")

[(1, False, False), (2, False, False), (3, True, True), (4, False, False), (5, None, None)]
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #0            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ ilike_escape(path, '%$%%',│
│            '$')           │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│            path           │
│                           │
└───────────────────────────┘

Limitations and errors

• The escape is empty or one ASCII character. Two characters, or one non-ASCII character such as é, fail with Invalid escape string. Escape string must be empty or one character. An escape taken from a column fails the same way if any row holds a longer value.
• A pattern can't end with the escape character when it is %. ilike_escape('ab', 'a%', '%') fails with Like pattern must not end with escape character!. With another escape character, ilike_escape('a', 'a$', '$') is false and doesn't fail.
• All three arguments are strings. A number as the escape fails with No function matches the given name and argument types 'ilike_escape(STRING_LITERAL, STRING_LITERAL, INTEGER_LITERAL)'; a number, date, boolean, BLOB or list as the string fails the same way. Cast a value to VARCHAR first.

run("SELECT ilike_escape('ab', 'a%', '%')")
run("SELECT ilike_escape('ab', 'ab', 1)")
run("SELECT ilike_escape(CAST(123 AS VARCHAR), '1%', '$')")

OSError: Like pattern must not end with escape character!
InternalError: Binder Error: No function matches the given name and argument types 'ilike_escape(STRING_LITERAL, STRING_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.
[(True,)]

Similar functions

run("SELECT ilike_escape('A%B', 'a$%b', '$'), 'A%B' ILIKE 'a$%b' ESCAPE '$', not_ilike_escape('A%B', 'a$%b', '$'), "
    "like_escape('A%B', 'a$%b', '$'), 'A%B' ILIKE 'a%b', contains('A%B', '%')")

[(True, True, False, False, 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: all the search and pattern functions.
• like_escape, ILIKE and LIKE.
• Operators and predicates.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.