Search Text Without Worrying About Letter Case

Imagine a customer database containing John Smith, JOANNA LEE, and jordan kim. If a user types jo into a search box, the application normally wants all three names to be considered, even though their capitalization is different.

This is the problem solved by case-insensitive pattern matching. Instead of treating uppercase and lowercase letters as different for the purpose of a search, the query can match text without requiring the user to enter the same capitalization stored in the database.

In the previous Module 11, we learned the basic SQL LIKE operator and its % and _ wildcards. This lesson builds directly on that idea:

PostgreSQL provides the convenient ILIKE operator for case-insensitive pattern matching. PostgreSQL also provides regular-expression operators such as ~ and ~*. Other databases solve the same general problem in different ways, including collations and functions such as REGEXP_LIKE.

What Case-Insensitive Pattern Matching Means

A case-sensitive comparison distinguishes between letters such as J and j. A case-insensitive search treats those differences as irrelevant for the comparison being performed.

Consider a customer named John Smith. A case-sensitive pattern could fail to match when the user searches for john. A case-insensitive pattern can match both forms.

The important point is that case-insensitive does not automatically mean substring. You still need a pattern that describes where the text may occur. For example, jo% means "starts with jo", while %jo% means "contains jo".

LIKE vs ILIKE

The previous lesson introduced LIKE. PostgreSQL's ILIKE keeps the familiar wildcard syntax but performs the pattern comparison without regard to letter case.

The general concept is portable, but the implementation is database-specific. When writing applications that support several database engines, always verify the matching and collation behavior of the target engine.

PostgreSQL ILIKE Operator

PostgreSQL's ILIKE operator is the simplest tool to reach for when the requirement is ordinary wildcard matching without case sensitivity.

SELECT name
 

FROM customers
WHERE name ILIKE 'jo%';

This query searches the name column for values beginning with jo, while allowing the stored letters to use different capitalization.

A row such as John Smith, JOANNA LEE, or jordan kim can match because the comparison is case-insensitive. A name such as Dara Sok does not match because it does not begin with jo.

ILIKE with % and _

The wildcard rules are familiar from LIKE. The percent sign % represents zero or more characters. The underscore _ represents a single character.

SELECT name
 

FROM customers
WHERE name ILIKE '%smith%';

This is a contains search. The value can contain characters before and after smith.

Practical PostgreSQL Examples

We will use a realistic customers table. The mixed capitalization is intentional because it demonstrates the real reason case-insensitive search is useful.

CREATE TABLE customers (
id BIGINT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(160) NOT NULL,
city VARCHAR(100),
job_title VARCHAR(120)
 

);

INSERT INTO customers
(id, name, email, city, job_title)
 

VALUES
(1, 'John Smith', '[john@example.com](mailto:john@example.com)', 'Phnom Penh', 'Backend Developer'),
(2, 'JOANNA LEE', '[joanna@example.com](mailto:joanna@example.com)', 'Phnom Penh', 'Product Designer'),
(3, 'jordan kim', '[jordan@example.com](mailto:jordan@example.com)', 'Siem Reap', 'Database Developer'),
(4, 'Dara Sok', '[dara@example.com](mailto:dara@example.com)', 'Phnom Penh', 'Software Engineer'),
(5, 'SAM WILSON', '[sam@example.com](mailto:sam@example.com)', 'Battambang', 'Data Analyst'),
(6, 'Alex Morgan', '[alex@example.com](mailto:alex@example.com)', 'Kampot', 'SQL Developer'),
(7, 'Sok Chan', '[sok@example.com](mailto:sok@example.com)', 'Phnom Penh', 'Support Engineer'),
(8, 'Joan Dara', '[joan.dara@example.com](mailto:joan.dara@example.com)', 'Phnom Penh', 'QA Engineer');

SELECT name
 

FROM customers
WHERE name ILIKE 'jo%';

SELECT name
 

FROM customers
WHERE name ILIKE '%smith%';

SELECT email
 

FROM customers
WHERE email ILIKE '%@example.com';

SELECT name, city
 

FROM customers
WHERE name ILIKE '%an%'
AND city ILIKE 'phnom%';

Case-Insensitive Regular Expressions in SQL

Wildcards are excellent for straightforward searches, but sometimes the matching rule is more complicated. You may need alternatives, anchors, character classes, repetitions, or other regular-expression features.

PostgreSQL supports POSIX-style regular expressions through dedicated operators. For case-insensitive regular-expression matching, the operator to know is ~*.

PostgreSQL ~* Operator

The ~* operator performs a case-insensitive regular-expression match.

SELECT name
 

FROM customers
WHERE name ~* '^jo';

The ^ anchor means the pattern must begin at the start of the string. Therefore, ^jo describes names beginning with jo.

Find a name containing a word

SELECT name
 

FROM customers
WHERE name ~* 'smith';

Because the pattern is not anchored, it can match the text smith within the value.

Match one of several names

SELECT name
 

FROM customers
WHERE name ~* '^(john|joanna|jordan)';

The parentheses create a group, while the vertical bar means "or". The leading ^ requires the matching alternatives to appear at the beginning of the name.

~ vs ~*

-- Case-sensitive regex
 

SELECT name
FROM customers
WHERE name ~ '^jo';

-- Case-insensitive regex
SELECT name
FROM customers
WHERE name ~* '^jo';

The pattern itself is the same. The operator changes the matching behavior.

POSIX Regular Expressions in PostgreSQL

PostgreSQL's regex operators use a POSIX-style regular-expression implementation. You do not need to memorize the entire syntax to begin using it. Start with a small set of useful concepts.

Regex syntax becomes useful when the search rule cannot be expressed clearly with only % and _.

REGEXP_LIKE and Database-Specific Syntax

One of the most important lessons in SQL is that the same business requirement can have different syntax across database engines.

PostgreSQL has ILIKE, ~, and ~*. Other major engines commonly rely on collations, regular-expression functions, or engine-specific options.

MySQL Case-Insensitive Search

MySQL does not use PostgreSQL's ILIKE operator. Case sensitivity for text comparisons is strongly influenced by the collation attached to the column, expression, or comparison.

SELECT name
 

FROM customers
WHERE name LIKE 'jo%';

Whether this comparison is case-insensitive depends on the collation being used. Many common MySQL collations are case-insensitive, but developers should not rely on the word LIKE alone to determine behavior.

Oracle REGEXP_LIKE Case-Insensitive Search

Oracle provides the REGEXP_LIKE condition for regular-expression matching. Its optional match-parameter argument can request case-insensitive behavior.

SELECT name
 

FROM customers
WHERE REGEXP_LIKE(name, '^jo', 'i');

The i match parameter tells Oracle to perform the regular-expression matching case-insensitively.

This is conceptually similar to PostgreSQL's ~*, but the syntax is different. The lesson is not to memorize one universal regex command; it is to recognize the common requirement and know how your database implements it.

SQL Server Case-Insensitive Collation Search

SQL Server commonly handles case sensitivity through collations. A collation containing _CI_ indicates case-insensitive behavior.

SELECT name
 

FROM customers
WHERE name COLLATE Latin1_General_100_CI_AS LIKE 'jo%';

The exact collation should be selected according to the application's language and data requirements. Do not treat one collation as universally correct for every database.

Sorting Search Results by Relevance

Finding matching rows is only part of a useful search feature. If a user searches for jo, it may be more helpful to display names beginning with jo before names that merely contain jo somewhere in the middle.

SQL can implement a simple rule-based ranking by calculating a relevance value and then sorting by it.

SELECT
name,
email,
CASE
    WHEN name ILIKE 'jo%' THEN 1
    WHEN name ILIKE '%jo%' THEN 2
    WHEN email ILIKE '%jo%' THEN 3
    ELSE 4
END AS relevance
 

FROM customers
WHERE name ILIKE '%jo%'
OR email ILIKE '%jo%'
ORDER BY relevance, name;

The query creates a simple relevance score. Lower numbers appear first because ORDER BY relevance sorts ascending by default.

ORDER BY CASE WHEN for Search Priority

CASE is useful because it converts business rules into sortable values. Instead of trying to force SQL's normal alphabetical ordering to understand what "more relevant" means, you explicitly define the priority.

ORDER BY
CASE
    WHEN name ILIKE 'jo' THEN 1
    WHEN name ILIKE 'jo%' THEN 2
    WHEN name ILIKE '%jo%' THEN 3
    ELSE 4
END,
name;

This technique is especially useful for application search boxes, autocomplete results, customer lookup screens, and administrative dashboards where a simple deterministic ranking is enough.

Full-Text Search vs ILIKE

ILIKE is useful for straightforward wildcard and substring matching. It is not a complete full-text search engine.

If the application needs tokenization, linguistic search, ranking, document search, or searching large text collections, investigate your database's full-text search capabilities. That does not mean full-text search is automatically better for every query.

Why ILIKE Can Become Slow

A query such as the following is easy to write:

SELECT name
 

FROM customers
WHERE name ILIKE '%smith%';

On a small table, this may be perfectly acceptable. As the table grows, however, the database may need to inspect many rows to determine which values contain the requested text.

Why the leading wildcard matters

A normal B-Tree index is naturally suited to ordered values and many equality or left-anchored range patterns. A pattern beginning with % does not provide a known starting position in the text.

That means a normal B-Tree index may not be able to efficiently narrow the search for a pattern such as %smith%. The exact plan depends on the database engine, version, expression, collation, available indexes, and data distribution.

Indexing for ILIKE Queries

Index strategy should follow the actual search pattern. An index that helps one pattern may not help another.

Prefix searches

A query such as name ILIKE 'jo%' is structurally different from name ILIKE '%jo%'. Prefix searches can sometimes be compatible with ordered index strategies, depending on PostgreSQL version, collation, expression, and index design.

Substring searches

For searches where the user can place the term anywhere inside the value, PostgreSQL's trigram extension can be a useful option.

PostgreSQL pg_trgm

PostgreSQL provides the pg_trgm extension for trigram-based text similarity and index support. Trigrams break text into groups of three characters, providing an index structure that can be useful for many similarity and substring searches.

A common PostgreSQL setup for substring searches looks like this:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
 

CREATE INDEX idx_customers_name_trgm
ON customers
USING GIN (name gin_trgm_ops);

After creating the index, the search can remain simple:

SELECT name
 

FROM customers
WHERE name ILIKE '%smith%';

The important idea is that the application query does not need to become complicated merely because the physical access strategy changed.

EXPLAIN ANALYZE
 

SELECT name
FROM customers
WHERE name ILIKE '%smith%';

B-Tree vs GIN for Text Search

Index types should be chosen according to the operations they need to support. GIN is not automatically faster than B-Tree, and B-Tree is not automatically the right choice for every text query.

The right choice depends on query shape, selectivity, table size, index size, write rate, PostgreSQL version, and the rest of the workload.

Regex Query Performance

Regular expressions are powerful, but power does not automatically mean poor performance. Likewise, a regex query is not automatically slow.

Performance depends on the expression, the amount of data, selectivity, available indexes, planner behavior, and the database implementation.

If all you need is a case-insensitive substring check, compare the readability and measured behavior of ILIKE against a regex before choosing the more complicated expression.

Practical Search Example

Imagine a customer-support application with a search box. Users can type a person's name or email address. The first version of the search looks like this:

SELECT id, name, email, city
 

FROM customers
WHERE name ILIKE '%jo%'
OR email ILIKE '%jo%';

Step 1 — Confirm the search requirement

The requirement is a simple case-insensitive substring search. There is no immediate reason to introduce regex.

Step 2 — Improve the result order

SELECT
id,
name,
email,
city,
CASE
    WHEN name ILIKE 'jo%' THEN 1
    WHEN name ILIKE '%jo%' THEN 2
    WHEN email ILIKE '%jo%' THEN 3
    ELSE 4
END AS relevance
 

FROM customers
WHERE name ILIKE '%jo%'
OR email ILIKE '%jo%'
ORDER BY relevance, name;

Now a name beginning with the search term gets priority over a name containing the term somewhere later, while email matches receive a lower priority.

Step 3 — Measure the query

EXPLAIN ANALYZE
 

SELECT
id,
name,
email,
city
FROM customers
WHERE name ILIKE '%jo%'
OR email ILIKE '%jo%';

Step 4 — Consider data size

If the customer table is small and the query runs occasionally, a sequential scan may be completely reasonable. Do not add specialized indexes just because they exist.

Step 5 — Consider trigram indexing when justified

CREATE EXTENSION IF NOT EXISTS pg_trgm;
 

CREATE INDEX idx_customers_name_trgm
ON customers
USING GIN (name gin_trgm_ops);

CREATE INDEX idx_customers_email_trgm
ON customers
USING GIN (email gin_trgm_ops);

These indexes may be useful for a workload containing frequent substring searches. Whether both are justified depends on the actual application workload, storage budget, write rate, and measured query plans.

Practice Exercises

SELECT name
 

FROM customers
WHERE name ILIKE 'jo%';

SELECT email
 

FROM customers
WHERE email ILIKE '%EXAMPLE%';

SELECT name
 

FROM customers
WHERE name ~* '^jo';

SELECT
name,
CASE
    WHEN name ILIKE 'jo%' THEN 1
    WHEN name ILIKE '%jo%' THEN 2
    ELSE 3
END AS relevance
 

FROM customers
WHERE name ILIKE '%jo%'
ORDER BY relevance, name;

EXPLAIN ANALYZE
 

SELECT name
FROM customers
WHERE name ILIKE '%smith%';

CREATE EXTENSION IF NOT EXISTS pg_trgm;
 

CREATE INDEX idx_customers_name_trgm
ON customers
USING GIN (name gin_trgm_ops);

EXPLAIN ANALYZE
SELECT name
FROM customers
WHERE name ILIKE '%smith%';

Common Mistakes to Avoid

Best Practices Checklist

Frequently Asked Questions

SQL Case-Insensitive Search Quiz

Conclusion: From LIKE to Smarter Text Search

Case-insensitive pattern matching becomes much easier once you understand the layers involved. Start with the basic pattern-matching concept from LIKE, then use PostgreSQL ILIKE when the same wildcard behavior should ignore letter case.

When the pattern itself becomes more sophisticated, PostgreSQL regular expressions provide another level of control. The ~ operator performs regex matching, while ~* provides case-insensitive regex matching.

The same business requirement looks different in other databases. MySQL commonly relies on collation behavior, Oracle provides REGEXP_LIKE with match parameters such as i, and SQL Server commonly uses case-insensitive collations.

Performance is the final part of the lesson. A simple query such as ILIKE '%term%' may be perfectly acceptable for a small table but become expensive as the dataset and search frequency grow. PostgreSQL's pg_trgm can be valuable for appropriate substring-search workloads, but an index should be justified by measurement rather than added automatically.

Related Lessons