---
title: "How to perform case-insensitive string matching with ILIKE in ClickHouse®"
excerpt: "Learn how to use the ILIKE operator in ClickHouse® for case-insensitive text search. Compare ILIKE vs LIKE vs match, optimize with ngram indexes, and build string matching APIs with Tinybird."
authors: "Cameron Archer"
categories: "AI Resources"
createdOn: "2025-10-09 00:00:00"
publishedOn: "2025-10-10 00:00:00"
updatedOn: "2025-10-10 00:00:00"
status: "published"
---

When you're building search features on top of ClickHouse{% sup %}®{% /sup %}, you typically want searches to match case insensitive results. Users expect to find results for iPhones regardless of whether they type "iPhone" or "iphone". The `ILIKE` operator handles this case-insensitive matching automatically, letting you search for text patterns without worrying about capitalization.

This guide covers how to perform case insensitive text matching in ClickHouse{% sup %}®{% /sup %} using `ILIKE`, when to use `ILIKE` instead of other string matching functions, and how to optimize text search query performance with specialized indexes. You'll also see how to build a production-ready search API using Tinybird's [managed ClickHouse{% sup %}®{% /sup %} platform](https://www.tinybird.co/clickhouse).

## What is ILIKE in ClickHouse{% sup %}®{% /sup %}

The `ILIKE` operator in ClickHouse{% sup %}®{% /sup %} performs pattern matching on strings without caring about uppercase or lowercase letters. When you search for 'apple' using `ILIKE`, it will match 'Apple', 'APPLE', 'aPpLe', and any other case variation in your data.

This operator works exactly like the `LIKE` operator, except it ignores case differences. You can use wildcard characters to create flexible search patterns: the percent sign `%` matches any number of characters (including zero), while the underscore `_` matches exactly one character.

```sql
SELECT product_name
FROM products
WHERE product_name ILIKE '%apple%';
-- "apple", "Apple", "aPpLe", "Apple MacBook Pro", "Red apples"
```

This query returns all product names containing "apple" regardless of how the letters are capitalized. The `%` wildcards on both sides tell ClickHouse{% sup %}®{% /sup %} to find "apple" anywhere in the string.

## ILIKE operator versus ilike function

ClickHouse{% sup %}®{% /sup %} gives you two ways to write the same case-insensitive search: `column ILIKE pattern` or `ilike(column, pattern)`. Both produce identical results, but the operator syntax reads more naturally if you're used to SQL.

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

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

Most developers find the operator form easier to read, especially when you're chaining multiple conditions together. In addition, the operator is commonly used across other SQL dialects (e.g. PostgreSQL).

### Operator rewrite during parsing

When you write `column ILIKE pattern`, ClickHouse{% sup %}®{% /sup %} automatically converts it to `ilike(column, pattern)` while parsing your query. This transformation happens before execution, so there's no performance difference between the two forms.

The operator syntax exists purely for readability. Under the hood, ClickHouse{% sup %}®{% /sup %} treats both forms identically.

### Why aliases matter for readability

The operator form makes your queries cleaner when you're combining multiple search conditions. Compare these two examples:

```sql
-- Easier to scan
WHERE email ILIKE '%@gmail.com' AND name ILIKE 'sarah%'

-- Harder to parse quickly
WHERE ilike(email, '%@gmail.com') AND ilike(name, 'sarah%')
```

If other people will read your queries, the operator syntax saves them a few seconds of mental parsing. Those seconds add up across a large codebase.

## Full syntax and wildcard rules

The `ILIKE` operator takes a column and a pattern string. The pattern can include regular text characters plus two special wildcards: `%` and `_`.

ClickHouse{% sup %}®{% /sup %} reads your pattern from left to right, matching characters against the string value. When it hits a wildcard, the matching behavior changes based on which wildcard you used.

### Percent and underscore wildcards

The `%` wildcard matches zero or more characters of any kind. You can put it at the start, middle, or end of your pattern.

```sql
-- Matches names starting with 'john'
SELECT name FROM users WHERE name ILIKE 'john%';

-- Matches names ending with 'smith'
SELECT name FROM users WHERE name ILIKE '%smith';

-- Matches names containing 'anne' anywhere
SELECT name FROM users WHERE name ILIKE '%anne%';
```

The `_` wildcard matches exactly one character. This works well when you know the string length but not all the characters.

```sql
-- Matches 'cat', 'car', 'can'
SELECT word FROM dictionary WHERE word ILIKE 'ca_';

-- Matches phone numbers like '555-1234'
SELECT phone FROM contacts WHERE phone ILIKE '555-____';
```

### Escaping special characters

When your data contains literal `%` or `_` characters that you want to search for, put a backslash `\` before them. Without the backslash, ClickHouse{% sup %}®{% /sup %} interprets them as wildcards.

```sql
-- Search for strings with a literal percent sign
SELECT description FROM products WHERE description ILIKE '%50\% off%';

-- Search for strings with an underscore
SELECT filename FROM files WHERE filename ILIKE '%test\_%';
```

### Handling UTF-8 and case folding

ClickHouse{% sup %}®{% /sup %} performs Unicode case conversion for `ILIKE` operations, which means it handles international characters correctly. The case conversion follows Unicode standards for Latin, Cyrillic, Greek, and most other alphabets.

```sql
-- Matches 'Müller', 'müller', 'MÜLLER'
SELECT name FROM customers WHERE name ILIKE 'müller';
```

For more precise control over case conversion in edge cases, you can still use `lowerUTF8()` with the `LIKE` operator instead.

```sql
-- Matches 'Müller', 'müller', 'MÜLLER'
SELECT name FROM customers WHERE lowerUTF8(name) LIKE 'müller';
```

## ILIKE vs LIKE vs match for case-insensitive search

ClickHouse{% sup %}®{% /sup %} offers three main approaches for pattern matching in strings. Each has different tradeoffs in terms of flexibility and speed.

The `LIKE` operator does case-sensitive matching. It runs faster than `ILIKE` because it skips the case conversion step, but it only finds exact case matches.

The `match()` function uses regular expressions for pattern matching. Regular expressions give you more flexibility than SQL wildcards, but they typically run slower on large datasets.

| Function | Case Sensitivity | Pattern Type | Best For |
| -------- | ---------------- | ------------ | -------- |
| `LIKE` | Sensitive | SQL wildcards | Known case patterns |
| `ILIKE` | Insensitive | SQL wildcards | Simple flexible search |
| `match()` | Configurable | Regex | Complex patterns |

### Performance benchmarks

The `LIKE` operator generally runs faster than `ILIKE` because it doesn't convert case. The difference becomes noticeable mainly on tables with billions of rows.

When you combine `lower()` or `lowerUTF8()` with `LIKE`, you're essentially doing what `ILIKE` does internally. The performance is similar, but `ILIKE` produces cleaner code.

```sql
-- Similar performance
WHERE lower(email) LIKE '%gmail.com'
WHERE email ILIKE '%gmail.com'
```

### When to prefer match or multiMatch

The `match()` function works better for complex patterns that SQL wildcards can't express, though [regex-based functions cannot leverage](https://tinybird-blog.ghost.io/text-search-at-scale-with-clickhouse) ngrambf_v1 indexes. For example, validating email formats or matching multiple domain patterns at once.

```sql
-- Use match() for complex patterns
WHERE match(email, '^[a-z0-9]+@(gmail|yahoo)\.com$')

-- Use ILIKE for simple patterns
WHERE email ILIKE '%@gmail.com'
```

The `multiMatchAny()` function checks multiple patterns in one pass, which can be faster than chaining several `ILIKE` conditions with `OR`.

## Speeding up ILIKE with ngram and tokenbf indexes

ClickHouse{% sup %}®{% /sup %} provides specialized indexes for text search that can dramatically speed up `ILIKE` queries on large tables. Without an index, `ILIKE` scans every row even on tables with billions of entries.

The index types that work with `ILIKE` pre-process your string data to enable faster substring lookups. They don't work for all query patterns, but when they do work, the speedup can be substantial.

### Creating an ngrambf_v1 index

The `ngrambf_v1` index type creates a bloom filter based on character n-grams. An n-gram is just a sequence of n characters, like "appl" or "pple" from "apple".

```sql
CREATE TABLE products (
    id UInt32,
    name String,
    INDEX name_ngram name TYPE ngrambf_v1(4, 512, 2, 0) GRANULARITY 1
) ENGINE = MergeTree()
ORDER BY id;
```

Similar configurations have achieved [88× speedup over non-indexed searches](https://www.tinybird.co/blog-posts/using-bloom-filter-text-indexes-in-clickhouse) in production workloads.

### Query planner behavior

ClickHouse{% sup %}®{% /sup %}'s query optimizer decides whether to use an n-gram index based on your pattern. The optimizer uses the index when the pattern includes enough non-wildcard characters to make index lookups worthwhile.

```sql
-- Index can help here
WHERE name ILIKE '%phone%'

-- Index can help here too
WHERE name ILIKE 'apple%'
```

You can check whether ClickHouse{% sup %}®{% /sup %} plans to use your index by running `EXPLAIN` before your query.

## Common mistakes that slow down ILIKE queries

Several patterns can make `ILIKE` queries run slowly even with proper indexing. Knowing these pitfalls helps you [write faster queries](https://www.tinybird.co/blog-posts/5-rules-for-writing-faster-sql-queries).

### Leading wildcard explosion

Patterns starting with `%` prevent some optimizations. The query planner can still use n-gram indexes for these patterns, but the performance gain is smaller compared to patterns with known prefixes.

```sql
-- Less efficient even with index
WHERE name ILIKE '%phone'

-- More efficient with index
WHERE name ILIKE 'phone%'
```

The leading wildcard prevents ClickHouse{% sup %}®{% /sup %} from eliminating rows based on how the string starts.

```sql
-- Slower: leading wildcard
WHERE description ILIKE '%warranty%'

-- Faster: known prefix
WHERE description ILIKE 'warranty%'
```

For the best performance, try to structure your queries to avoid leading wildcards when possible. If you frequently search with leading wildcards, consider storing a reversed version of the text in a separate column.

### Data type mismatches

Applying `ILIKE` to non-string columns forces ClickHouse{% sup %}®{% /sup %} to convert the data type before matching. This conversion happens for every row and adds overhead.

```sql
-- Inefficient: converts numbers to strings
WHERE user_id ILIKE '%123%'

-- Better: explicit conversion
WHERE toString(user_id) ILIKE '%123%'

-- Best: use numeric comparison
WHERE user_id = 123
```

When working with numeric or date columns, use the appropriate comparison operators instead of pattern matching.

### Negating ILIKE incorrectly

Using `NOT ILIKE` can prevent index usage and force full table scans. ClickHouse{% sup %}®{% /sup %} [cannot optimize negative operators](https://clickhouse.com/blog/a-simple-guide-to-clickhouse-query-optimization-part-1) due to Bloom filter limitations.

```sql
-- Slower: negative condition
WHERE email NOT ILIKE '%@spam.com'

-- Consider alternatives
WHERE domain != 'spam.com'
```

If you need to exclude certain patterns frequently, consider adding a boolean column or separate table to track exclusions. You can check [function support for different search indexes in Tinybird](https://www.tinybird.co/docs/sql-reference/engines/mergetree#functions-support).

## End-to-end example: build a parameterized ILIKE API with Tinybird

[Tinybird](https://tinybird.co) is a [managed ClickHouse{% sup %}®{% /sup %} service](https://www.tinybird.co/clickhouse) that lets you create APIs from SQL queries without managing infrastructure. This example shows how to build a searchable API using `ILIKE` for case-insensitive text matching.

First, install the Tinybird CLI and start the local development environment:

```bash
curl -L tinybird.co | sh
tb login
tb local start
```

### Ingest sample data source

Create a file named `products.ndjson` with sample product data:

```json
{"product_id": 1, "name": "iPhone 15 Pro", "category": "Electronics", "description": "Latest Apple smartphone"}
{"product_id": 2, "name": "MacBook Air", "category": "Electronics", "description": "Lightweight laptop from Apple"}
{"product_id": 3, "name": "Samsung Galaxy S24", "category": "Electronics", "description": "Android flagship phone"}
{"product_id": 4, "name": "Dell XPS 13", "category": "Electronics", "description": "Windows ultrabook"}
{"product_id": 5, "name": "iPad Pro", "category": "Electronics", "description": "Apple tablet with M2 chip"}
```

Create the data source and load the data:

```bash
tb datasource create products --file products.ndjson
tb build
tb datasource append products --file products.ndjson
```

### Create the pipe with a pattern parameter

Create a file named `search_products.pipe` that defines your search API:

```text
TOKEN search_products_read READ

DESCRIPTION >
    Search products by name or description using case-insensitive matching

NODE search_products_node
SQL >
    %
    SELECT
        product_id,
        name,
        category,
        description,
        sum(amount) AS total_revenue
    FROM products
    WHERE
        name ILIKE {{String(pattern, '%')}}
        OR description ILIKE {{String(pattern, '%')}}
    GROUP BY product_id, name, category, description
    ORDER BY product_id
    LIMIT {{Int32(limit, 10)}}

TYPE endpoint
```

The `{{String(pattern)}}` syntax, part of Tinybird's templating language, creates a [query parameter](https://www.tinybird.co/docs/forward/work-with-data/query-parameters) that API consumers can pass when calling your endpoint.

The default value `%` returns all products if no pattern is provided.

### Deploy and query the API

Deploy your pipe to Tinybird Cloud to create a hosted API:

```bash
tb --cloud deploy
```

Get the API endpoint URL:

```bash
tb --cloud endpoint url search_products --language curl
```

This outputs a curl command you can use to query your API:

```bash
curl -X GET "https://api.us-east.tinybird.co/v0/pipes/search_products.json?pattern=%apple25apple%25&limit=5&token=p.ey...token"
```

The API returns JSON with matching products:

```json
{
  "meta": [
    {"name": "product_id", "type": "Int64"},
    {"name": "name", "type": "String"},
    {"name": "category", "type": "String"},
    {"name": "description", "type": "String"}
    {"name": "total_revenue", "type": "Float64"}
  ],
  "data": [
    {"product_id": 1, "name": "iPhone 15 Pro", "category": "Electronics", "description": "Latest Apple smartphone", "total_revenue": 10295921.59},
    {"product_id": 2, "name": "MacBook Air", "category": "Electronics", "description": "Lightweight laptop from Apple", "total_revenue": 2859059.19},
    {"product_id": 5, "name": "iPad Pro", "category": "Electronics", "description": "Apple tablet with M2 chip", "total_revenue": 7401298.10}
  ],
  "rows": 3 ...
}
```

You can change the `pattern` parameter to search for different terms. The `ILIKE` operator handles all case variations automatically.

## Next steps to ship real-time search faster

Tinybird eliminates the infrastructure complexity of running ClickHouse{% sup %}®{% /sup %} clusters. Instead of spending weeks setting up servers, configuring replication, and tuning performance, you can focus on building search and real-time analytics features.

The platform handles data ingestion, query optimization, and API hosting in one integrated service. This means you go from raw data to a production API in minutes instead of weeks.

[Sign up for a free Tinybird account](https://cloud.tinybird.co/signup) to start building case-insensitive search APIs with ClickHouse{% sup %}®{% /sup %}. The free tier includes enough resources to build and test real applications.

## FAQs about case-insensitive search in ClickHouse{% sup %}®{% /sup %}

### How do I perform ILIKE against multiple patterns in ClickHouse{% sup %}®{% /sup %}?

You can chain multiple `ILIKE` conditions with `OR` operators to match any of several patterns. For better performance with many patterns, consider using `multiMatchAny()` with regular expressions that include case-insensitive flags.

```sql
-- Multiple ILIKE conditions
WHERE name ILIKE '%apple%' OR name ILIKE '%samsung%' OR name ILIKE '%google%'

-- Alternative with multiMatchAny
WHERE multiMatchAny(name, ['(?i)apple', '(?i)samsung', '(?i)google'])
```

### Does ILIKE work with Cyrillic or other non-Latin alphabets in ClickHouse{% sup %}®{% /sup %}?

Yes, `ILIKE` supports UTF-8 case folding for most Unicode characters including Cyrillic, Greek, Arabic, and other alphabets. ClickHouse{% sup %}®{% /sup %} automatically handles case conversion for international character sets during pattern matching.

```sql
-- Works with Cyrillic text
WHERE city ILIKE '%москва%'  -- matches 'Москва', 'МОСКВА', 'москва'
```