Skip to main content
Allows matching of strings based on comparison to a pattern. ILIKE is normally used as part of a WHERE clause. ILIKE is case-insensitive; use LIKE for case-sensitive pattern matching. Note that Firebolt uses the POSIX locale, which means that it only classifies the ASCII letters “A” through “Z” and “a” through “z” as letters.

​
Syntax

​
Parameters

​
Return Type

BOOLEAN

​
Example

Find nicknames from the players table that partially match the string “Joe” and any following characters as follows:

Rows: 7Execution time: 2.46ms

​
Example using a non-constant pattern

The pattern doesn’t have to be a literal. You can match each row against a pattern stored in another column. For example, given a filters table that holds one pattern per row, join it with players to return the nicknames that match any of the patterns:
If the pattern column contains JOE% and %LONG, this returns nicknames such as joedavis, joe79, and joellong regardless of case.

​
Unicode Behavior

Firebolt uses the POSIX locale, therefore ILIKE case insensitivity is limited to ASCII characters. The uppercase and lowercase versions of non-ASCII characters are not matched. Returns false — Æ is non-ASCII, so ENCYCLOPÆDIA does not match encyclopædia:

Rows: 1Execution time: 4.99ms

Returns true — the non-ASCII Æ matches itself, and the ASCII letters match case-insensitively:

Rows: 1Execution time: 4.86ms

Returns false — Ü is non-ASCII, so MÜNCHEN does not match München:

Rows: 1Execution time: 5.79ms

Returns true — the non-ASCII Ü matches itself, and the ASCII letters match case-insensitively:

Rows: 1Execution time: 6.58ms

Returns false — Greek letters are non-ASCII and are never case-folded:

Rows: 1Execution time: 5.16ms