ILIKE

Reference material for ILIKE function

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

<expression> ILIKE '<pattern>'

Parameters

Parameter	Description	Supported input types
<expression>	Any expression that evaluates to TEXT	TEXT
<pattern>	Specifies the pattern to match (case-insensitive). The pattern can be a constant or any TEXT expression, including a column reference.	TEXT. SQL wildcards are supported: * Use an underscore (_) to match any single character * Use a percent sign (%) to match any number of any characters, including no characters.

Return Type

BOOLEAN

Example

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

SELECT
	playerid, nickname, email
FROM
	players
WHERE
	nickname ILIKE 'Joe%'
ORDER BY playerid;

playerid int null	nickname text null	email text null
145	joel53	[e-mail]
1356	joel35	[e-mail]
2823	joe12	[e-mail]
3232	joe30	[e-mail]
3872	joemata	[e-mail]
4655	joel68	[e-mail]
4880	joel47	[e-mail]

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:

SELECT
    p.nickname, f.pattern
FROM
    players p
JOIN
    filters f ON p.nickname ILIKE f.pattern;

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:

SELECT 'ENCYCLOPÆDIA' ILIKE 'encyclopædia';

?column? boolean
False

Rows: 1Execution time: 4.99ms

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

SELECT 'ENCYCLOPÆDIA' ILIKE 'encyclopÆdia';

?column? boolean
True

Rows: 1Execution time: 4.86ms

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

SELECT 'MÜNCHEN' ILIKE 'München';

?column? boolean
False

Rows: 1Execution time: 5.79ms

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

SELECT 'MÜNCHEN' ILIKE 'mÜnchen';

?column? boolean
True

Rows: 1Execution time: 6.58ms

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

SELECT 'Πσρ⋈' ILIKE 'πΣΡ%';

?column? boolean
False

Rows: 1Execution time: 5.16ms