A search for Jonson can miss the Johnson already in your table. Trigram matching compares small pieces of spelling and ranks candidates that share them. It gives you suggestions to review without pretending that similar names prove the same identity.
Define the Trigram Matching Contract
A trigram is a sequence of three adjacent characters. Johnson contains overlapping pieces such as joh and ohn after normalization. A missing letter changes several pieces while leaving others intact. Comparing sets of pieces provides a spelling based signal. It differs from LIKE, which needs an explicit wildcard pattern, and from a phonetic comparison of how words sound.
I decide normalization before discussing the score. Trim outer spaces, choose a case rule, and decide how punctuation and accents should behave. The examples use lowercase text and a binary collation for exact gram equality. They do not remove accents or punctuation. If your matching contract does, apply the identical normalization during indexing and searching.
Keep the Original and Normalized Words
Run these blocks in one session. The original word remains available for display. The normalized value serves the matching contract. A permanent implementation needs a controlled write path that maintains both values. The source examples are fictional search inputs, and no timing is measured here.
CREATE TABLE #Word
(
WordID int NOT NULL PRIMARY KEY,
OriginalWord nvarchar(100) NOT NULL,
NormalizedWord nvarchar(100) COLLATE Latin1_General_100_BIN2 NOT NULL
);
INSERT #Word(WordID,OriginalWord,NormalizedWord)
SELECT WordID,WordText,LOWER(LTRIM(RTRIM(WordText)))
FROM (VALUES(1,N'Johnson'),(2,N'Jonson'),(3,N'Johnston'),
(4,N'Jackson'),(5,N'Jensen'),(6,N'Jones')) AS v(WordID,WordText);
CREATE TABLE #Gram
(
Gram nvarchar(3) COLLATE Latin1_General_100_BIN2 NOT NULL,
WordID int NOT NULL,
PRIMARY KEY(Gram,WordID)
);Empty normalized strings should be rejected or routed to an explicit exact lookup. The n-gram index is not an excuse to search every row for an empty input. Set input length limits too. Very long values generate more index entries and need a different design from short names. Keep the normalization policy versioned so changes trigger an intentional rebuild.
Generate Distinct Three Character Pieces
Pad each word with a start and end marker. These markers make boundary positions contribute to the score. For this contract, reserve the caret and dollar characters for boundaries and reject them in input names. GENERATE_SERIES supplies character positions. It requires SQL Server 2022 or later with database compatibility level 160 or higher.
INSERT #Gram(Gram,WordID)
SELECT DISTINCT SUBSTRING(p.PaddedWord,g.value,3),w.WordID
FROM #Word AS w
CROSS APPLY(VALUES(N'^'+w.NormalizedWord+N'$')) AS p(PaddedWord)
CROSS APPLY GENERATE_SERIES(1,LEN(p.PaddedWord)-2,1) AS g;
CREATE INDEX IX_Gram_WordID ON #Gram(WordID) INCLUDE(Gram);
SELECT w.OriginalWord,g.Gram
FROM #Word AS w JOIN #Gram AS g ON g.WordID=w.WordID
ORDER BY w.WordID,g.Gram;DISTINCT turns repeated pieces into a set. That means a word containing the same trigram several times stores it once. The score below follows set semantics too. A frequency based method needs counts instead. Keep those designs separate. The Gram leading key supports candidate lookup, while the WordID index helps retrieve a word's pieces and maintain its entries.
Build the Search Pieces With the Same Rules
Normalize the search term exactly as stored words. The temporary primary key removes duplicate search grams. Keep this input bounded and reject reserved boundary markers. Parameterize the term in an application implementation. No dynamic SQL is needed to generate the pieces or join them to the stored n-gram index.
DECLARE @search nvarchar(100)=N'Jonson';
SET @search=LOWER(LTRIM(RTRIM(@search)));
IF @search=N'' OR @search LIKE N'%^%' OR @search LIKE N'%$%'
THROW 50001,'Enter a nonempty name without boundary markers.',1;
CREATE TABLE #SearchGram
(Gram nvarchar(3) COLLATE Latin1_General_100_BIN2 NOT NULL PRIMARY KEY);
DECLARE @padded nvarchar(102)=N'^'+@search+N'$';
INSERT #SearchGram(Gram)
SELECT DISTINCT SUBSTRING(@padded,value,3)
FROM GENERATE_SERIES(1,LEN(@padded)-2,1);This demonstration's three character pieces use the specified collation's string behavior. Supplementary Unicode characters and visible grapheme clusters deserve their own policy in multilingual names. Do not assume three storage units always mean three visible letters. Test the actual scripts with the names and collations your application accepts before defining the score as a user facing promise.
Score Trigram Matching by Shared Pieces
Count shared distinct grams for each candidate. Divide that count by the number of distinct grams in the union of the search and candidate sets. This is Jaccard similarity. Multiplying by a decimal value avoids integer division. The threshold below is an example tuning input. Choose your production threshold from labeled examples of correct and incorrect matches.
DECLARE @minimum_score decimal(6,2)=20;
WITH shared AS
(
SELECT g.WordID,COUNT_BIG(*) AS SharedGrams
FROM #Gram AS g JOIN #SearchGram AS q ON q.Gram=g.Gram
GROUP BY g.WordID
), totals AS
(
SELECT WordID,COUNT_BIG(*) AS WordGrams FROM #Gram GROUP BY WordID
), scored AS
(
SELECT w.WordID,w.OriginalWord,s.SharedGrams,
100.0*s.SharedGrams/
NULLIF(t.WordGrams+(SELECT COUNT_BIG(*) FROM #SearchGram)-s.SharedGrams,0)
AS SimilarityPct
FROM shared AS s
JOIN totals AS t ON t.WordID=s.WordID
JOIN #Word AS w ON w.WordID=s.WordID
)
SELECT TOP(5) WordID,OriginalWord,SharedGrams,SimilarityPct
FROM scored WHERE SimilarityPct>=@minimum_score
ORDER BY SimilarityPct DESC,WordID;The stable WordID tie breaker makes a capped result predictable. Words sharing no gram are absent, rather than receiving a returned zero score. For a large permanent index, maintain each word's distinct gram count alongside the index so this query does not repeatedly aggregate every word's entries. Verify that maintained count whenever rebuilding or updating the pieces. With the sample rows, Jonson matches itself at 100, Johnson scores about 44, and Jones about 22. Johnston shares only the two boundary pieces, so it falls below the threshold.
Compare a Phonetic Signal
SOUNDEX produces a phonetic code, and DIFFERENCE compares those codes on a zero through four scale. The signal favors pronunciation patterns rather than exact spelling overlap. It has language and collation limitations. Compare it on your actual name population instead of assuming an English phonetic rule fits every supported language.
DECLARE @search nvarchar(100)=N'Jonson';
SELECT WordID,OriginalWord,SOUNDEX(OriginalWord) AS PhoneticCode,
DIFFERENCE(@search,OriginalWord) AS PhoneticSimilarity
FROM #Word
ORDER BY PhoneticSimilarity DESC,WordID;I compare false positives as carefully as attractive matches. Two unrelated names can sound alike. In the sample, Jensen gets the same J525 code as Johnson, while the trigram score does not return it at all. Two correct variants can share few trigrams. Combining signals can help rank suggestions, but each additional rule needs testing. A confidence label should describe the method and evidence, not imply that a mathematical percentage is the probability of one real person being another.
Keep Trigram Matching Current as Words Change
When a word changes, replace its gram rows in the same transaction as the normalized value and stored gram count. Handle deletes too. Batch rebuilds need a controlled cutover so a query never mixes normalization versions. Use a unique pair key to enforce set semantics and a foreign key in the permanent design to protect source identity.
Test exact names, missing letters, transposed letters, repeated pieces, one character inputs, punctuation, and accents. A single transposition changes several neighboring grams. Very short words provide little evidence even with padding. What error costs more in your workflow: missing a suggestion or suggesting the wrong identity? Let that answer guide the ranking and review process.
Use Suggestions Without Automatic Identity Merges
Return the original spelling and enough authorized context for a reader to choose. Keep exact lookup as the first path when identity is already known. Trigram matching helps a search box recover from spelling errors. It should not silently join financial records, merge customers, or bypass a unique identity requirement.
I keep a small reviewed test set for the common mistakes the application sees. Track rejected suggestions as well as accepted ones. Compare plan reads and index maintenance on representative data before deployment. A search helper earns trust by making useful candidates visible and preserving uncertainty where the spelling alone cannot decide.
Related reading on this blog: The Intricacies of T-SQL String Comparison: LIKE VS '=' and Case-Sensitive Search.
A similar spelling is not a confirmed identity, it is a candidate for review.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
