Typo-tolerant, accent-insensitive, phonetic search — native on Informix.
A native Informix extension that finds the row that was probably meant, not only the row that was typed exactly. Trigram similarity, edit distance, phonetic matching and a trigram index, in ordinary SQL — the same function names and semantics as the PostgreSQL pg_trgm and fuzzystrmatch extensions, verified equal to PostgreSQL row by row.
Finds the row that was meant
Exact predicates — = and LIKE — miss the moment the data or the query carries a typo, a transposition, an accent or a phonetic difference. This extension absorbs all four. A clerk who types lopes finds López; Smith and Smyth join.
Native, in ordinary SQL
Trigram, edit-distance and phonetic functions plus a trigram index run inside the engine and are called from ordinary SQL. Fuzzy scoring combines with tenant, status and date filters in a single transactional statement, against the source of truth.
The same SQL as PostgreSQL
The function names and behaviour of pg_trgm and fuzzystrmatch — similarity, word_similarity, levenshtein, jaro_winkler, soundex, dmetaphone. The identical query runs on Informix and PostgreSQL and returns the same rows.
Accent folding built in
Every argument is folded to ASCII before matching, so José, Jose and Josef match with no wrapper. On raw PostgreSQL the same match needs the separate unaccent extension and an explicit call every time.
How a fuzzy match is found
How it works
A string is folded to ASCII, lower-cased, and cut into trigrams —
overlapping three-character windows. The similarity of two
strings is the Jaccard overlap of their trigram sets: the fraction of
trigrams they share, a score between 0 and 1. It tolerates typos,
transpositions and word reordering, it needs no dictionary, and it is
language-independent. word_similarity scores the best
matching extent inside a longer field, so a surname is found wherever it
sits in a full name.
Edit-distance functions measure closeness a different way — the number of
single-character edits between two strings. levenshtein
counts insertions, deletions and substitutions;
damerau_levenshtein adds the adjacent transposition that is
the most common human typo; jaro_winkler rewards a shared
prefix, the strongest signal in a person or place name. Phonetic
functions — soundex, difference and Double
Metaphone — encode a word by how it sounds, so Robert and
Rupert collide on the same key.
What the extension provides
Each function stands on its own; the trigram index makes the similarity path fast at scale.
Trigram similarity
The general-purpose measure: similarity, word_similarity, strict_word_similarity and show_trgm, computed as the trigram-set overlap of two strings. Ranked with ORDER BY similarity(...) DESC, thresholded with a single knob that trades recall for precision.
Edit distance
Precise character-level distance for short strings — names, SKUs, account numbers, postal codes: levenshtein, levenshtein_less_equal, damerau_levenshtein and jaro_winkler. Used as an exact reranking step over the candidate set the trigram index recalls.
Phonetic matching
Sounds-like keys for names spelled by ear: soundex and difference, plus Double Metaphone (dmetaphone, dmetaphone_alt) for the cross-language spellings Soundex cannot reach. A phonetic code is a fixed short value, so an equality probe over a stored code column is an ordinary B-tree lookup.
The trigram index
A native Informix secondary access method — CREATE INDEX … USING trgm_am — that turns a fuzzy search over millions of rows into a candidate lookup instead of a sequential scan. The index recalls a small candidate set; an exact function reranks only those rows. Measured on Informix 15, roughly seventeen times faster than the scan at 50,000 rows, with results identical to the full scan.
Why in the engine, not a second cluster
The usual answer to typo-tolerant search is a separate search cluster beside the database — another copy of the data to keep in sync, another failure mode, another system to secure and staff. Performing the match inside the engine keeps fuzzy scoring consistent with the transactional source of truth, lets it combine with ordinary SQL filters in one statement, and removes the second datastore entirely.
This does not replace Informix Basic Text Search; it complements it. BTS answers find this document — Boolean queries, phrase search, stemming. This extension answers find the row that was probably meant — the approximate-matching layer BTS does not provide. Together they cover both exact document retrieval and typo-tolerant, relevance-ranked fuzzy search.
Informix fuzzy search and PostgreSQL pg_trgm
| PostgreSQL + pg_trgm | Informix with ifxtools | |
|---|---|---|
| Function surface | The reference: similarity, word_similarity, levenshtein, jaro_winkler, soundex, difference and dmetaphone, across pg_trgm and fuzzystrmatch. | ✓ The same function names and the same semantics, verified equal to PostgreSQL row by row — so the identical SQL runs on both engines. |
| Accent handling | Accent-sensitive. Matching an accented name from an unaccented query needs the separate unaccent extension and an explicit wrapper on every call. | ✓ Accent folding is built into every function and on by default. An unaccented, lower-case query finds the accented, upper-case, misspelled variants without a wrapper. |
| Verification | The reference implementation the behaviour is defined against. | ✓ Measured against the reference by exhaustive per-row comparison — 122,000 assertions with zero mismatches across similarity, edit distance and phonetics. |
| Trigram index | A mature GIN gin_trgm_ops index. | ✓ A native trigram access method (CREATE INDEX … USING trgm_am) — a capability the engine ships none of otherwise. Persisted, compressed and maintained atomically with the table. |
| Index latency at millions of rows | GIN is well ahead — roaring-bitmap posting lists and a vectorised merge keep latency low at that scale. | ✓ The index replaces the sequential scan with a candidate lookup and is fast; the residual latency gap to mature GIN at the largest scales is real and named honestly. |
| LIKE and operators | A bare LIKE '%word%' and the % similarity operator route to the index transparently, chosen by the planner. | ✓ Routed through an explicit text_like or text_matches predicate paired with an exact recheck; a bare LIKE '%word%' still scans. The extensibility model cannot bind a custom index to the built-in LIKE. |
| Scalar throughput on a full scan | A mature C implementation, the fastest per-row baseline. | ✓ Two to six times slower per row on a full scan — which is exactly why the trigram index and the two-stage recall-and-rerank pattern carry the load. |
| Result sets | The reference answer. | ✓ Identical to PostgreSQL, verified row-for-row across the fuzzy methods at 50,000 and 100,000 rows. |
Where it earns its place
The estate already holds the data. These are the questions it can now answer inside SQL.
Master data and deduplication
Golden-record matching across systems: finding the customer entered twice with a transposed letter, a dropped accent or a spelling variant, and collapsing the duplicates. Trigram similarity needs no dictionary and is language-independent.
Name and party screening
Customer and counterparty screening across Spanish, French, German, Nordic and Slavic names, where the same person arrives with or without diacritics across channels. Built-in folding matches José, Jose and Josef without hand-written normalisation.
Search that tolerates typos
Product-catalogue and customer lookup where the query and the stored value differ by a handful of characters. A search for Jonhson surfaces Johnson, Johnston and Johansson, ranked by confidence, directly in the query.
Retiring LIKE '%word%'
A leading-wildcard LIKE is both incomplete and the slowest possible plan. word_similarity finds a term at any position, folds accent and case, tolerates the typo, and routes through the trigram index — the correct replacement on human-entered data.
Call-centre and phonetic lookup
Finding a caller by a name they spell aloud and an agent mistypes, through phonetic keys. Double Metaphone catches cross-language spellings a plain Soundex misses.
Record linkage and entity resolution
Probabilistic matching of person records across systems — the classic Fellegi-Sunter setting — where jaro_winkler is the canonical name-comparison feature and edit distance provides the precise rerank.
Before you evaluate fuzzy search.
Is this genuinely the same SQL as PostgreSQL pg_trgm and fuzzystrmatch?
Yes. The function names and their semantics match PostgreSQL, so an identical query runs on Informix and PostgreSQL. Parity is measured, not asserted: a multi-engine harness loads the same corpus into both engines and compares results, recording roughly 122,000 comparisons with zero mismatches for similarity, levenshtein, soundex and difference, and the trigram index returns the same rows as a full scan at every size tested.
Does the fuzzy match run inside the engine, or in an external process?
Inside the engine. The trigram, edit-distance and phonetic functions and the trigram index are native extensions used from ordinary SQL. That is what lets a fuzzy score combine with tenant, status, permission and date filters in a single transactional statement, and keeps the match consistent with the transactional source of truth — with no separate search cluster to operate, secure and keep in sync.
A fuzzy predicate sounds like it scans every row. How is it fast?
Through a two-stage pattern. A trigram predicate is not ordinal, so a B-tree cannot serve it directly; written naively it evaluates the function on every row. The native trigram index instead recalls a small candidate set — the rows whose trigrams overlap the query above a pruning bound — and an exact function reranks only those candidates. On Informix 15 this measured roughly seventeen times faster than the sequential scan at 50,000 rows, and the advantage grows with row count because the scan is dead-linear while the index is a candidate lookup.
How are accents and multilingual names handled?
Every argument is folded to ASCII before matching, using a codepoint table ported verbatim from the PostgreSQL unaccent rules. Folding is on by default inside every function, so an unaccented query finds an accented name and the reverse. This is an advantage over raw pg_trgm, which is accent-sensitive and needs the separate unaccent extension with an explicit wrapper on every call. Folding being always on is also the one behaviour to be aware of: a per-session opt-out for diacritic-exact matching is planned.
Where is PostgreSQL still ahead?
In three places, and we state them plainly. PostgreSQL's GIN trigram index is more mature — roaring-bitmap posting lists and a vectorised merge give it lower latency at the millions-of-rows scale. A bare LIKE '%word%' and the % operator route to the GIN index transparently on PostgreSQL, whereas Informix needs an explicit text_like or text_matches predicate paired with an exact recheck, because the engine cannot bind a custom index to the built-in LIKE. And the scalar functions are two to six times slower per row than mature PostgreSQL C on a full scan, which is why the index and the rerank pattern matter. The result sets themselves are identical, verified row-for-row.
Do we have to give up LIKE '%word%'?
Only where it was never doing the job. A literal LIKE '%word%' is accent- and case-sensitive and cannot use an index because of the leading wildcard. word_similarity is the complete and indexable replacement: it finds the term at any position, folds accent and case, tolerates the typo, and routes through the trigram index. Where exact LIKE semantics must be kept, the extension can serve them from the same index through the text_like and text_ilike predicates, paired with the exact LIKE as a recheck.
Does this replace Informix Basic Text Search?
No, it complements it. BTS is a capable full-text engine for document retrieval — Boolean queries, phrase search, stemming, synonym dictionaries — and keeps that role. This extension adds the approximate-matching layer BTS does not provide: similarity scoring, edit distance, phonetics and index-accelerated typo tolerance. Together they cover both exact document search and fuzzy, relevance-ranked lookup.
Which Informix versions are supported, and is there a Soundex caveat?
The extension is verified on Informix 14 and 15. One caveat: Informix 15 ships a built-in, un-droppable soundex() that is not accent-folded. The installer keeps that built-in and exposes the accent-folding, PostgreSQL-exact code as trgm_soundex(); on Informix 14 the extension's own soundex() is used. difference() folds accents on both releases.