How to Use ILIKE in Snowflake for Case-Insensitive Matching
In Snowflake, use the
ILIKE operator to perform case-insensitive pattern matching in SQL queries. It works like LIKE but ignores letter case, allowing you to match strings regardless of uppercase or lowercase letters.Syntax
The ILIKE operator in Snowflake is used to compare a string to a pattern, ignoring case differences.
expression ILIKE pattern: Returns true ifexpressionmatches thepatterncase-insensitively.expression: The string or column to test.pattern: The pattern to match, which can include wildcards like%(any sequence of characters) and_(any single character).
sql
SELECT 'Snowflake' ILIKE 'snow%';
Output
TRUE
Example
This example shows how to use ILIKE to find rows where the name column matches a pattern regardless of case.
sql
CREATE OR REPLACE TABLE fruits (name STRING); INSERT INTO fruits VALUES ('Apple'), ('banana'), ('Cherry'), ('apricot'); SELECT name FROM fruits WHERE name ILIKE 'a%';
Output
NAME
Apple
apricot
Common Pitfalls
Common mistakes when using ILIKE include:
- Using
LIKEwhen case-insensitive matching is needed, which will fail to match different cases. - Forgetting that
ILIKEsupports wildcards%and_, so exact matches require no wildcards. - Assuming
ILIKEworks with non-string types without casting.
sql
/* Wrong: Case-sensitive match misses 'banana' */ SELECT name FROM fruits WHERE name LIKE 'B%'; /* Right: Case-insensitive match finds 'banana' */ SELECT name FROM fruits WHERE name ILIKE 'b%';
Output
/* Wrong query output: */
NAME
/* Right query output: */
NAME
banana
Quick Reference
| Operator | Description | Case Sensitivity | Wildcards Supported |
|---|---|---|---|
| LIKE | Matches string to pattern | Case-sensitive | % and _ |
| ILIKE | Matches string to pattern | Case-insensitive | % and _ |
Key Takeaways
Use ILIKE in Snowflake for case-insensitive pattern matching in SQL queries.
ILIKE supports % and _ wildcards like LIKE but ignores letter case.
Avoid using LIKE when you need to match strings regardless of case.
ILIKE works only with string expressions; cast other types if needed.
Test your patterns carefully to ensure correct matches with wildcards.
