Search not matching accented names ¶

A support agent types muller and finds nothing. The customer is in the database as Müller. Typing Müller finds them; typing MÜLLER finds them on some pages and not others; typing Muller finds them never.

Search is a comparison, and every comparison of human text is a decision about which differences count. Here the code decided that an umlaut counts, which is exactly the opposite of what the person typing wanted.

Root cause: the query and the data are folded differently, or not at all ¶

A substring search compares two strings byte by byte. Müller and muller differ in one character, so the match fails — correctly, by the rules it was given.

Most implementations fix half of it. Lowercasing both sides handles case, which is the difference people notice first, and leaves accents untouched. So MÜLLER starts matching Müller and muller still does not, which produces the confusing pattern where search works sometimes.

There is a second, subtler asymmetry. If the application folds the query but the stored value was never folded, the comparison happens between a folded string and an unfolded one and matches less than either would alone. This is common when folding is added later: someone adds unaccent() to the query and the results get worse.

The third failure is invisible until the table grows. Folding inside the query — WHERE lower(unaccent(name)) LIKE '%muller%' — is correct and cannot use an index on name, so it scans every row. It works in development and times out in production.

Minimal reproducible example ¶

-- Neither of these finds Müller
SELECT * FROM customers WHERE name LIKE '%muller%';
SELECT * FROM customers WHERE lower(name) LIKE '%muller%';

-- This one does, and scans the whole table doing it
SELECT * FROM customers WHERE lower(unaccent(name)) LIKE '%muller%';

The fix: a folded column, indexed, folded on both sides ¶

CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Generated: the database keeps it in step with the source, no application code required.
ALTER TABLE customers
  ADD COLUMN name_folded text
  GENERATED ALWAYS AS (lower(unaccent(name))) STORED;

CREATE INDEX customers_name_folded_trgm
  ON customers USING gin (name_folded gin_trgm_ops);

// The query is folded by the same rule the column uses.
const rows = await db.query(
  'SELECT * FROM customers WHERE name_folded LIKE $1 ORDER BY name COLLATE de_icu',
  [`%${fold(term)}%`]
);

A generated column is worth the small storage cost: it cannot drift from the source, an import that bypasses the application still populates it, and a change to the folding rule is a single migration rather than a backfill plus a code change.

Choosing how aggressively to fold ¶

Folding is a spectrum, and each step widens the matches while blurring distinctions.

Case folding is uncontroversial: nobody searching for a name intends the capitalisation to matter.

Accent folding is what this page is about, and it is right for search in Latin-script languages where readers routinely omit diacritics because their keyboard makes them awkward. It is wrong in languages where the accented letter is a distinct letter — in Swedish, a and å are different letters, and folding them together produces matches a Swedish reader considers wrong.

Compatibility folding — NFKD — additionally collapses ligatures, fullwidth forms and superscripts. Useful for pasted text from documents and PDFs, and lossy in ways that rarely matter for search.

Transliteration across scripts, so Мюллер matches Muller, is a different and much larger problem that needs a transliteration library rather than a normalization form. It is worth doing only when your data genuinely spans scripts for the same entities.

The pragmatic answer for a Latin-script product is NFKD plus mark stripping plus lowercasing, with a per-locale exception list for languages where a diacritic is a letter. The exception list is short, and its absence is why Nordic users complain about search that everyone else likes.

Ranking, and why folding should not decide it ¶

Folding widens what matches; it should not flatten what ranks. A reader typing Müller with the umlaut probably wants the exact spelling first, and a reader typing muller will accept either.

SELECT *,
  -- exact spelling first, then folded matches, then by locale-correct order
  (name = $1) AS exact,
  similarity(name_folded, $2) AS score
FROM customers
WHERE name_folded LIKE $3
ORDER BY exact DESC, score DESC, name COLLATE de_icu
LIMIT 20;

Keeping the original value in the ranking is what stops folding from making search feel worse for the readers who typed carefully. It is also what makes a search box usable for a name that folds together with a common word — the folded match still appears, and it appears below the exact one.

The two-tier search most products actually need ¶

Teams often frame this as a choice between the database and a search engine, and for a customer or product lookup the useful arrangement is usually both, with a clear boundary.

Tier one is exact and structural. An identifier, an order number, an email address, a phone number in E.164. These are looked up rather than searched: the reader knows the value, the match must be exact after canonicalisation, and the result is a single row. Folding is irrelevant here, and applying it makes the lookup worse by returning near-matches nobody asked for.

Tier two is fuzzy and human. Names, company names, addresses, free text. This is where folding, trigrams and ranking belong, and where the reader expects to see several plausible results and choose.

Routing a query to the right tier is mostly a matter of shape. A string of digits with the length of an order number is a lookup. A string containing an at-sign is an email lookup. Everything else is a name search. Doing that dispatch explicitly, rather than running one query that tries to satisfy both, produces better results for each — and it avoids the common failure where an order number search returns fifty fuzzy matches ranked above the exact one.

The same split decides where a dedicated search engine earns its cost. Tier one never needs one: a unique index answers it in a millisecond. Tier two benefits once the corpus is large or the ranking requirements grow beyond similarity — at which point the folding rules move into the engine analyzers, and the symmetry requirement described above becomes a configuration question rather than a code one.

Verification ¶

test.each([
  ['muller', 'Müller'],
  ['MÜLLER', 'Müller'],
  ['jose', 'José'],
  ['strasse', 'Straße'],   // needs an explicit ß → ss rule; unaccent alone misses it
])('%s finds %s', async (term, stored) => {
  await seed({ name: stored });
  expect(await search(term)).toContainEqual(expect.objectContaining({ name: stored }));
});

The Straße row is the one that catches an incomplete folding implementation. Mark stripping does nothing for ß, because it is a letter rather than a base plus a diacritic, so an explicit replacement is required — and German readers search for strasse constantly.

When to escalate ¶

If matching is correct but slow, check that the query can use the index. A LIKE pattern with a leading wildcard needs a trigram index; a prefix search can use a plain one; a full-text search needs a different index type entirely.

If some rows never match, they may be stored in a decomposed form that the folding does not handle. Normalizing on write, as in duplicate usernames from Unicode normalization, removes the whole class.

If your search runs in a dedicated engine rather than the database, the folding lives in an analyzer there and the same symmetry rule applies — the query analyzer and the index analyzer must be the same, which is the single most common misconfiguration in those systems.

FAQ ¶

Related ¶

• Unicode Text Handling & Collation — the folding and collation model behind this.
• Duplicate usernames from Unicode normalization — the same folding used for identity rather than search.
• Postgres and application sort order disagree — ordering the results this query returns.
• Internationalizing Forms & Input — normalizing the values before they are ever searched.

Part of Unicode Text Handling & Collation.