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

<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
160joedavis[email protected]
519joe79[email protected]
3692joeli[email protected]
3891joel11[email protected]
4233joellong[email protected]
4627joebowen[email protected]

​
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