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

ParameterDescriptionSupported input types
<expression>Any expression that evaluates to TEXTTEXT
<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:

playeridnicknameemail
160joedaviscgarcia@example.org
519joe79jennifer10@example.net
3692joelicperez@example.net
3891joel11joanncain@example.net
4233joellongmillerholly@example.net
4627joebowenamandalewis@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