Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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 if expression matches the pattern case-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 LIKE when case-insensitive matching is needed, which will fail to match different cases.
  • Forgetting that ILIKE supports wildcards % and _, so exact matches require no wildcards.
  • Assuming ILIKE works 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

OperatorDescriptionCase SensitivityWildcards Supported
LIKEMatches string to patternCase-sensitive% and _
ILIKEMatches string to patternCase-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.