LIKE and ILIKE: Substring Search Across SQL Engines

Substring search in SQL: how LIKE and ILIKE work, the % and _ wildcards, escaping, and how the same operation maps to MongoDB's $regex with $options: 'i'.

“Find a substring inside a string” is one of the most common operations there is. SQL engines handle it with LIKE / ILIKE plus a pattern.

`string LIKE/ILIKE pattern`

Returns whether the string matches the LIKE pattern
- string LIKE pattern, string NOT ILIKE pattern
- The difference: ILIKE is case-insensitive.

Pattern rules

%: any number of any characters (including zero).
_: exactly one character.
\: escape character, used to match a literal %, _, or \.
Example: %minor_action_hr%

Mapped onto Mongo, this operation is $regex ($options: "i" is the equivalent of ILIKE) → MongoDB operators and vocabulary

References

ClickHouse String Search Functions — https://clickhouse.com/docs/en/sql-reference/functions/string-search-functions

Related: back to the Cross-Engine DB Query overview, or see it inside the portable SQL core.

Tags #sql #databases