ILIKE
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). | TEXT constant. 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%';
Returns:
| playerid | nickname | |
|---|---|---|
| 160 | joedavis | cgarcia@example.org |
| 519 | joe79 | jennifer10@example.net |
| 3692 | joeli | cperez@example.net |
| 3891 | joel11 | joanncain@example.net |
| 4233 | joellong | millerholly@example.net |
| 4627 | joebowen | amandalewis@example.net |
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:
SELECT 'ENCYCLOPÆDIA' ILIKE 'encyclopædia'; -- returns false
SELECT 'ENCYCLOPÆDIA' ILIKE 'encyclopÆdia'; -- returns true
SELECT 'MÜNCHEN' ILIKE 'München'; -- returns false
SELECT 'MÜNCHEN' ILIKE 'mÜnchen'; -- returns true
SELECT 'Πσρ⋈' ILIKE 'πΣΡ%'; -- returns false