1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

Case Insensitive Search in SQL: LOWER, LIKE, COLLATE and Indexes

A case insensitive search finds “asha@example.com” when the user types “ASHA@Example.com”. It sounds trivial, but the method you choose decides whether the query uses an index or scans the whole table, and whether names like José and Straße match at all. We compared five approaches in SQLite with query plans and timings, and summarise how PostgreSQL, MySQL and SQL Server differ.

9 min read
Case insensitive search in SQL: LOWER, LIKE, COLLATE and indexes

Key takeaways

  • A case insensitive search on LOWER(email) scanned all 300,000 rows (43 ms) until we created an index on that exact expression; then it was an index search taking under 0.01 ms.
  • In SQLite, LIKE and COLLATE NOCASE ignore case only for ASCII letters: “josé” did not match “JOSÉ”.
  • SQLite’s lower('JOSÉ') returned “josÉ”; a custom function using Python’s casefold() matched both.
  • A COLLATE NOCASE UNIQUE column rejected a second email differing only in case.

Users do not care about capital letters. They type their email in any case, search for “iphone” when the product is “iPhone”, and expect a username to be unique whether it is “Sam” or “sam”. A case insensitive search in SQL handles that, but each database has different defaults and each technique has a different effect on performance and correctness.

We ran every test on SQLite 3.50.4 through Python. Notes on PostgreSQL, MySQL and SQL Server are based on their documentation and the research cited in this article; we did not run those systems.

Why case insensitive search is not trivial

  • Defaults differ: MySQL and SQL Server usually compare text case-insensitively out of the box; PostgreSQL and SQLite compare case-sensitively.
  • Functions block indexes: wrapping a column in LOWER() hides it from a normal index.
  • Unicode is hard: some languages have letters with no simple one-to-one upper and lower case, such as German ß and Turkish dotless i.

Our test data

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, name TEXT);
-- Asha@Example.com    Asha
-- asha@example.com    asha dup
-- BEN@example.com     Ben
-- José@example.com    José
-- JOSÉ@EXAMPLE.COM    JOSÉ
-- straße@example.com  Strasse

CREATE TABLE big (id INTEGER PRIMARY KEY, email TEXT);   -- 300,000 rows like User123@Example.com
CREATE INDEX ix_email ON big(email);

Method 1: plain equals is case-sensitive

SELECT name FROM users WHERE email = 'asha@example.com';
-- [('asha dup',)]

Only the exactly matching lowercase row came back; “Asha@Example.com” was missed. That is SQLite and PostgreSQL behaviour. In MySQL with its default collation, the same query would return both rows, which surprises developers moving between databases.

Method 2: LOWER on both sides

SELECT id FROM big WHERE lower(email) = 'user123@example.com';
-- plan: SCAN big          43.48 ms

Converting both sides to lowercase is the most portable approach and works in every database. But the query plan showed SCAN big: the index on email could not be used, because the index stores original values, not lowercase ones. On 300,000 rows that took 43 ms per query, and it grows linearly with the table. The fix is an index on the same expression, shown below.

Method 3: LIKE

SELECT name FROM users WHERE email LIKE 'asha@%';
-- [('Asha',), ('asha dup',)]

In SQLite, LIKE is case-insensitive for ASCII letters by default, so both rows matched. In PostgreSQL, LIKE is case-sensitive and ILIKE is the case-insensitive version. Turning on PRAGMA case_sensitive_like = ON in SQLite made LIKE 'user123@%' return 0 rows against our capitalised data. Patterns are explained in our SQL LIKE operator guide.

Method 4: COLLATE NOCASE

SELECT name FROM users WHERE email = 'ASHA@EXAMPLE.COM' COLLATE NOCASE;
-- [('Asha',), ('asha dup',)]

CREATE TABLE u2 (email TEXT COLLATE NOCASE UNIQUE);

A collation defines how text is compared and sorted. SQLite’s built-in NOCASE collation treats A to Z and a to z as equal. You can apply it in one query, as above, or declare it on the column so every comparison, sort and index on that column is case-insensitive automatically. Other databases have equivalents with different names, listed later.

The accent problem

SELECT name FROM users WHERE email LIKE 'josé@%';
-- [('José',)]                  JOSÉ@EXAMPLE.COM was not matched

SELECT lower('JOSÉ'), upper('straße');
-- ('josÉ', 'STRAßE')

This is the trap most tutorials skip. SQLite’s LIKE, NOCASE, lower() and upper() only fold the 26 ASCII letters. “É” stayed uppercase, so “josé” and “JOSÉ” did not match, and upper('straße') left the ß untouched. For an English-only system that may be acceptable. For names, cities and international product data, it silently misses records. SQLite’s optional ICU extension adds full Unicode folding; we did not have it in our build.

Method 5: a Unicode-aware function

db.create_function("pylower", 1, lambda s: s.casefold() if s else s, deterministic=True)
SELECT name FROM users WHERE pylower(email) = pylower('JOSÉ@EXAMPLE.COM');
-- [('José',), ('JOSÉ',)]

"straße".casefold() == "STRASSE".casefold()    # True

From Python we registered a SQL function that calls str.casefold(), which implements full Unicode case folding, so both José rows matched. Casefold even maps ß to “ss”. Because we marked it deterministic, SQLite also allows an index on pylower(email), though that index then depends on the function being registered on every connection. In PostgreSQL, ICU-based collations play this role on the server.

Indexes and query plans

Query on 300,000 rows Plan Time
email = ? (exact case) SEARCH using ix_email under 0.01 ms
lower(email) = ? SCAN big 43.48 ms
email = ? COLLATE NOCASE SCAN big 12.13 ms
lower(email) = ? after CREATE INDEX ix_lower ON big(lower(email)) SEARCH using ix_lower under 0.01 ms
email = ? COLLATE NOCASE after CREATE INDEX ix_nocase ON big(email COLLATE NOCASE) SEARCH using ix_nocase under 0.01 ms
email LIKE 'user123@%' with ix_nocase present SEARCH using ix_nocase (range) 0.01 ms

The pattern is the same in every database: an index only helps if it was built with the same expression or collation the query uses. The ordinary ix_email index could serve neither lower(email) nor COLLATE NOCASE. An expression index on lower(email), or an index declared with the NOCASE collation, turned a full scan into a direct lookup thousands of times faster. SQLite even used the NOCASE index for a prefix LIKE, because its LIKE is case-insensitive by default. Note that a leading wildcard such as '%example.com' cannot use any of these indexes. For reading plans, see our SQL query optimization guide and indexes guide.

Tip: if you always search a column case-insensitively, store a normalised copy (for example email_lower) or declare the column with a case-insensitive collation, and index that. Then every query is simple and fast.

Case-insensitive uniqueness

INSERT INTO u2 VALUES ('Asha@Example.com');
INSERT INTO u2 VALUES ('asha@example.com');
-- IntegrityError: UNIQUE constraint failed: u2.email

Usernames and emails should usually be unique regardless of case, or two accounts can exist for one person. A unique constraint on a NOCASE column rejected the second insert. In PostgreSQL the equivalent is a unique index on lower(email) or a citext column. See our SQL constraints guide for unique constraints in general.

How other databases handle it

Database Default Case-insensitive options
PostgreSQL Case-sensitive ILIKE, lower() with an expression index, the citext extension, nondeterministic ICU collations
MySQL Case-insensitive (default _ci collations) Already insensitive; use a _bin or _cs collation for case-sensitive
SQL Server Usually case-insensitive (CI collation) COLLATE Latin1_General_CI_AS per query or column
SQLite Case-sensitive for =, ASCII-insensitive for LIKE COLLATE NOCASE, expression index on lower(), ICU extension

In PostgreSQL, ILIKE with a pattern cannot use a normal B-tree index; a trigram index from the pg_trgm extension handles it. Nondeterministic collations also have limits with LIKE in older versions. Check the documentation for your exact version before choosing.

Which method should you use?

  • Portable and simple: LOWER(col) = LOWER(?) plus an expression index on LOWER(col).
  • Always case-insensitive column: declare a case-insensitive collation (SQLite NOCASE, MySQL _ci, PostgreSQL citext or ICU) and index it.
  • Partial matches: LIKE or ILIKE with a prefix pattern, or full-text search for words inside text.
  • International text: use Unicode-aware folding (ICU collations or casefold in the application), and test with accented and non-Latin names.
  • Normalise on write: lowercasing emails before storing them makes every later query trivial.

The same ideas appear in application code: Python’s regex module has an IGNORECASE flag, and string helpers are covered in our SQL string functions guide.

Final thoughts

A case insensitive search is easy to write and easy to get wrong. Choose one approach per column, make sure an index matches the expression or collation your queries use, and decide explicitly how accented letters should behave. In our tests, the difference between a scan and an index search was 43 milliseconds against a few microseconds, and the difference between ASCII-only and Unicode folding was a customer named José who could not be found.

Frequently asked questions

How do I make a SQL query case insensitive?

Compare LOWER of the column with LOWER of the value, use a case-insensitive collation, or in PostgreSQL use ILIKE. Add an index on the same expression or collation so the query stays fast.

Is SQL case sensitive?

Keywords are not, but data comparisons depend on the database and collation. MySQL and SQL Server usually ignore case by default, while PostgreSQL and SQLite compare case-sensitively.

Why is my LOWER query slow?

A normal index stores original values, so a query on LOWER of the column cannot use it and scans the table. Create an index on LOWER of the column.

Does COLLATE NOCASE work with accented letters?

Not in SQLite. NOCASE only folds the 26 ASCII letters, so an accented capital does not match its lowercase form. Use the ICU extension or a custom function for full Unicode folding.

What is ILIKE?

ILIKE is the case-insensitive version of LIKE in PostgreSQL. It is not standard SQL, so other databases use LOWER with LIKE or a case-insensitive collation instead.

Keep learning

Work with Stack Quora

Need help with testing, WordPress or SEO?

We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Confusion Matrix Explained: Precision, Recall, F1 and Thresholds in Python

Confusion matrix explained with Python: TP, FP, FN, TN, precision, recall, F1, the accuracy trap, threshold choice by cost and…

Read article →
Software Development

API Pagination: Offset vs Cursor vs Keyset, Tested on 1 Million Rows

API pagination explained: offset vs keyset and cursor pagination timed on 1 million rows, duplicates when data changes, the ties…

Read article →
DevOps

Git Submodule Explained: Add, Clone, Update and Remove Safely

Git submodule explained: add, clone, update –init, update –remote, the detached HEAD trap, foreach and clean removal, tested step by…

Read article →
Test Automation

Pytest tmp path Explained: tmp_path, tmp_path_factory and Temp Files

Pytest tmp_path explained: per-test temporary folders, tmp_path_factory for shared files, monkeypatch.chdir, legacy tmpdir, retention and basetemp.

Read article →
Software Testing

Test Case Prioritization: 6 Techniques Compared With APFD

Test case prioritization compared: risk, coverage, cost, history and random orders scored with APFD and time to find all faults…

Read article →
Databases & SQL

Full Text Search in SQL: FTS5, Ranking, Phrases and Speed

Full text search in SQL with SQLite FTS5: MATCH queries, phrases, prefixes, NEAR, stemming, BM25 ranking, snippets, two gotchas and…

Read article →
Databases & SQL

Generated Columns in SQL: Virtual vs Stored, Indexes and Limits

Generated columns in SQL explained: virtual vs stored, automatic updates, write errors, storage cost, indexing, JSON fields and support in…

Read article →
Software Development

Object Freeze in JavaScript: Shallow vs Deep, Seal and const

Object freeze in JavaScript explained: freeze vs seal vs const, strict mode errors, shallow vs deep freeze, frozen arrays, Map…

Read article →
Software Development

Python hashlib: SHA-256, File Hashes, HMAC and Password Hashing

Python hashlib explained: SHA-256 for strings and files, algorithm speeds, why passwords need PBKDF2 or scrypt, safe verification and HMAC…

Read article →
Software Development

Python UUID Explained: uuid1, uuid4, uuid5, uuid7 and Database Keys

Python UUID explained: uuid4, uuid5, uuid1 privacy, time-ordered uuid7, parsing, storage formats and UUIDs as database keys, tested on Python…

Read article →
Artificial Intelligence

Random Forest in Python: Bagging, Feature Sampling and OOB From Scratch

Random forest in Python from scratch: bootstrap sampling, feature sampling, number of trees, out-of-bag evaluation and impurity vs permutation importance.

Read article →
Software Development

MIME Types Explained: Content-Type, nosniff and a Common Types List

MIME types explained and tested in a browser: Content-Type, nosniff, scripts and stylesheets with wrong types, a common MIME types…

Read article →
DevOps

Git Clean Explained: Remove Untracked Files Without Regret

Git clean explained with dry runs: -n, -f, -d, -x, -X, exclusions, paths, nested repositories and interactive mode, plus a…

Read article →
Test Automation

Pytest approx: Comparing Floats With rel, abs and Collections

Pytest approx explained: default tolerances, the near-zero trap, rel and abs, lists and dicts, NaN, NumPy, and how it differs…

Read article →
Software Testing

Accessibility Testing: WCAG Checklist, Automated Tools and Manual Checks

Accessibility testing explained: WCAG basics, an axe-core scan of a broken and a fixed page, what automation missed, contrast checks…

Read article →
Databases & SQL

SQL UNPIVOT: Turn Columns Into Rows, 4 Ways Tested

SQL UNPIVOT explained: turn columns into rows with UNION ALL, CROSS JOIN with CASE, JSON functions or native UNPIVOT, with…

Read article →
Software Development

Intl NumberFormat in JavaScript: Currency, Compact, Percent and Units

Intl.NumberFormat in JavaScript: locales, currency, compact notation, percent, units, rounding, formatToParts, the non-breaking space trap and formatter reuse.

Read article →
Databases & SQL

SQL EXPLAIN Plan: How to Read Query Plans and Fix Slow Queries

SQL EXPLAIN plan explained: read scans, index searches, covering indexes and temporary sorts, with before and after timings for five…

Read article →
Artificial Intelligence

Decision Tree in Python: Gini, Entropy and Overfitting From Scratch

Decision tree in Python from scratch: Gini impurity, entropy, information gain, best splits, readable rules, and how depth and leaf…

Read article →
DevOps

Git Blame Explained: Find Who Changed a Line, and Why

Git blame explained with a real repository: ignore whitespace, line ranges and functions, ignore-revs files for formatting commits, moved code…

Read article →
Test Automation

Pytest Benchmark: Measure Speed and Catch Performance Regressions

Pytest benchmark tutorial: the benchmark fixture, reading results, parametrized and pedantic benchmarks, saving baselines and failing the build on regressions.

Read article →
Software Testing

Defect Density: Formula, Worked Examples and How to Use It

Defect density explained: the formula, a five-module worked example, per KLOC vs per function point, severity weighting, defect removal efficiency…

Read article →
Databases & SQL

Case Insensitive Search in SQL: LOWER, LIKE, COLLATE and Indexes

Case insensitive search in SQL: LOWER, LIKE, COLLATE NOCASE and expression indexes compared with query plans, plus accented letters and…

Read article →
Databases & SQL

ON DELETE CASCADE in SQL: CASCADE vs SET NULL vs RESTRICT

ON DELETE CASCADE in SQL tested: CASCADE vs SET NULL vs RESTRICT, multi-level cascades, the SQLite foreign key pragma, row…

Read article →
Artificial Intelligence

Logistic Regression in Python: Sigmoid, Log Loss and Gradient Descent From Scratch

Logistic regression in Python from scratch: sigmoid, log loss, gradient descent, odds ratios, thresholds, sigmoid overflow and separable data, tested…

Read article →
DevOps

Git Worktree Explained: Work on Two Branches at Once

Git worktree explained: add, list, remove, prune, lock and detached worktrees, a hotfix workflow, and the errors you will hit,…

Read article →
Test Automation

Pytest ini Explained: addopts, markers, testpaths and Precedence

Pytest ini explained: testpaths, addopts, strict markers, xfail_strict, filterwarnings, minversion, config file precedence and command-line overrides, tested.

Read article →
Software Testing

Test Estimation Techniques: WBS, Three Point PERT and Monte Carlo

Test estimation techniques worked through: WBS, three point PERT, total uncertainty, a Monte Carlo check, percentage rules and Wideband Delphi.

Read article →
Artificial Intelligence

Linear Regression in Python: Least Squares From Scratch, With R Squared

Linear regression in Python from scratch: closed form, NumPy least squares and gradient descent, R squared, RMSE, outliers and curved…

Read article →
DevOps

Git Remote Explained: Add, Set-URL, Rename, Remove and Push URLs

Git remote explained: add, set-url, separate push URLs, rename, remove and prune, plus how to avoid leaking tokens in remote…

Read article →
Test Automation

Pytest Mock: mocker.patch, spy, side_effect and autospec Explained

Pytest mock explained: mocker.patch, return_value, side_effect, spy, autospec and PropertyMock, with tested examples for APIs, clocks and retries.

Read article →
Software Testing

System Testing Explained: Types, Process and a Real End-to-End Example

System testing explained: types, entry and exit criteria, the process, and a real end-to-end test suite run against a small…

Read article →
Artificial Intelligence

Naive Bayes Classifier Explained: Build a Spam Filter in Python

Naive Bayes classifier explained by building a spam filter in Python: Bayes rule, the naive assumption, Laplace smoothing, log probabilities…

Read article →
DevOps

Git Branch Explained: Create, Switch, Rename, Delete and Recover

Git branch explained with tested commands: list, create, switch, rename, delete, merged checks, recovering deleted branches, remote tracking and naming.

Read article →
Test Automation

Pytest raises: Testing Exceptions, match and excinfo

Pytest raises explained with tested examples: match regexes, excinfo, custom and chained exceptions, parametrized error cases, pytest.warns and common mistakes.

Read article →
Software Testing

Non Functional Testing: Types, Metrics and a Real Load Test

Non functional testing explained: 10 key types, measurable requirements, latency percentiles and what a small real load test revealed about…

Read article →