ClickHouse ILIKE Operator: Case-Insensitive Pattern Matching

The ILIKE operator in ClickHouse performs case-insensitive pattern matching on strings. It works exactly like LIKE, but ignores uppercase and lowercase differences when comparing characters.

Syntax

ClickHouse supports both operator and function syntax:

-- Operator syntax
SELECT * FROM users WHERE name ILIKE '%john%';

-- Function syntax
SELECT * FROM users WHERE ilike(name, '%john%');

Both forms are equivalent. The operator syntax is more readable and conventional.

Official ClickHouse Documentation on LIKE / ILIKE

Wildcard Characters

Examples

-- Substring search (case-insensitive)
SELECT product_name
FROM products
WHERE product_name ILIKE '%apple%';
-- Matches: "Apple iPhone", "APPLE Watch", "green apple", "Pineapple"

-- Prefix search
SELECT email
FROM users
WHERE email ILIKE 'admin%';
-- Matches: "admin@example.com", "ADMIN@corp.net", "Admin.User@test.org"

-- Suffix search
SELECT filename
FROM files
WHERE filename ILIKE '%.pdf';
-- Matches: "report.PDF", "Document.pdf", "SCAN.Pdf"

-- Single character wildcard
SELECT code
FROM products
WHERE code ILIKE 'A_C';
-- Matches: "ABC", "abc", "AxC", "a1c"

-- Combining wildcards
SELECT message
FROM logs
WHERE message ILIKE '%error%timeout%';
-- Matches any message containing "error" followed later by "timeout", case-insensitive

-- Negation
SELECT *
FROM events
WHERE event_type NOT ILIKE '%test%';
-- Excludes: "Test Event", "TEST_RUN", "load_test"

ILIKE vs. LIKE

Use LIKE when you know the exact case of your data (e.g., normalized columns). Use ILIKE when case is inconsistent or you're building user-facing search.

Performance Considerations

Full Table Scans

ILIKE '%substring%' (with leading wildcard) cannot use the primary index and results in a full column scan. This is inherent to substring search — the database must check every value.

For large tables, this can be slow. Strategies to mitigate:

1. Add a bloom filter index:

   ALTER TABLE logs
       ADD INDEX message_idx message
       TYPE tokenbf_v1(10240, 3, 0)
       GRANULARITY 4;
   
   ALTER TABLE logs MATERIALIZE INDEX message_idx;
   

   Token bloom filters can skip granules that definitely don't contain the search term, reducing the scan. Note that bloom filters work with LIKE and hasToken but have limited benefit for ILIKE since they're case-sensitive internally.

2. Store a lowercased column:

   ALTER TABLE logs
       ADD COLUMN message_lower String
       MATERIALIZED lower(message);
   

   Then query with LIKE on the lowercased column instead of ILIKE on the original. This enables bloom filter indexes to work effectively.

3. Limit the scan with additional filters: Always combine ILIKE with filters on indexed columns (e.g., date ranges, service names) to reduce the data scanned:

   SELECT * FROM logs
   WHERE event_date >= '2025-01-01'
       AND event_date <= '2025-01-31'
       AND message ILIKE '%connection refused%';
   

Prefix Searches Are Faster

ILIKE 'prefix%' (no leading wildcard) is significantly faster because ClickHouse can potentially use the primary index if the column is part of the sort key. Prefer prefix patterns when possible.

NOT ILIKE

NOT ILIKE prevents index usage entirely and forces a full scan, since ClickHouse can't use bloom filters to prove a value doesn't match. Use sparingly on large tables.

Alternatives to ILIKE

Depending on your use case, these alternatives may perform better:

lower() + LIKE

SELECT * FROM logs WHERE lower(message) LIKE '%error%';

Functionally equivalent to ILIKE, but allows you to create a materialized lowercased column with proper indexes.

hasToken() / hasTokenCaseInsensitive()

SELECT * FROM logs WHERE hasTokenCaseInsensitive(message, 'error');

Searches for a whole word/token rather than a substring. Much faster than ILIKE when combined with tokenbf_v1 indexes, because it matches exact tokens rather than arbitrary substrings.

match() for Regex

SELECT * FROM logs WHERE match(lower(message), 'error|warning|critical');

For complex pattern matching beyond simple wildcards, use match() with regular expressions. More powerful but slower than LIKE/ILIKE.

multiSearchAnyCaseInsensitive()

SELECT * FROM logs
WHERE multiSearchAnyCaseInsensitive(message, ['error', 'warning', 'critical']);

Searches for multiple substrings in one pass. More efficient than chaining multiple ILIKE conditions with OR.

Best Practices

1. Avoid leading wildcards on large tables: ILIKE '%term%' scans the full column. Pair with date or partition filters to limit scope.

2. Normalize case at ingestion: If case-insensitive search is a primary use case, store a lowercased copy of the column and use LIKE with proper indexes instead.

3. Use hasTokenCaseInsensitive for word search: When searching for whole words rather than arbitrary substrings, token-based functions with bloom filter indexes are dramatically faster.

4. Combine with indexed filters: Always narrow the search with primary key or partition key filters before applying ILIKE.

5. Prefer ILIKE over lower() + LIKE in ad-hoc queries: For one-off analysis, ILIKE is more readable and performs identically. Reserve the lower() + materialized column approach for production query patterns.

Frequently Asked Questions

Q: Does ILIKE work with UTF-8 / Unicode strings?

Yes. ClickHouse's ILIKE handles UTF-8 strings and performs case-insensitive matching according to Unicode case folding rules. Characters like 'ß' (German) and accented characters are handled correctly.

Q: Can I use ILIKE in a WHERE clause with other conditions?

Yes, combine freely with AND/OR:

SELECT * FROM events
WHERE status = 200
    AND path ILIKE '/api/v2/%'
    AND user_agent ILIKE '%chrome%';

Q: Is there a way to make ILIKE use an index?

ILIKE with a prefix pattern (no leading %) can leverage the primary index if the column is in the sort key. For substring patterns, consider a tokenbf_v1 or ngrambf_v1 data skipping index, or a materialized lowercased column with a bloom filter index.

Q: How does ILIKE performance compare to LIKE?

On the same data and pattern, ILIKE is marginally slower than LIKE due to internal case normalization. The difference is typically negligible — the dominant cost is data scanning, not case conversion. If you're seeing slow queries, the issue is almost certainly the scan size, not the case-insensitivity overhead.

Q: Can I escape wildcard characters in ILIKE patterns?

Yes, use the backslash as an escape character: ILIKE '%100\%%' matches strings containing the literal text "100%".