A DISTINCT query cannot recognize that punctuation, casing, and repeated spaces are the only differences between two labels. Finding near-duplicate rows requires an explicit normalization rule and a review of the matches it creates.
Decide What Makes Near-Duplicate Rows Match
Normalization deliberately removes information. Decide which characters and spacing distinctions the business considers irrelevant before writing a replacement expression. A punctuation difference in a display label can be harmless, while the same difference in a part number, legal name, or external identifier can distinguish two valid records.
I ask for examples of records that must stay separate before asking for examples that should match. That exposes overly aggressive rules early. Keep positive matches and intentional nonmatches in the test set. A normalization rule that combines everything into one group has achieved excellent compression and poor judgment.
This article uses REGEXP_REPLACE, introduced in SQL Server 2025. The examples prepare comparison values in temporary tables and leave the source values untouched. They identify candidates under a chosen rule; they do not establish that the records represent the same real-world entity or authorize deletion.
Create a Reviewable Source Sample
The sample stores an original label, creation time, and verification flag alongside a stable row identifier. Those attributes support a later survivor proposal. They do not claim that older records are always correct or verified records always complete. The ranking policy still needs the owner's acceptance for the actual dataset.
CREATE TABLE #SourceLabels
(
SourceID int NOT NULL PRIMARY KEY,
OriginalLabel nvarchar(200) NULL,
CreatedAt datetime2 NOT NULL,
IsVerified bit NOT NULL
);
INSERT #SourceLabels VALUES
(1,N'Blue Adapter','2026-09-01',1),
(2,N' BLUE ADAPTER ','2026-09-02',0),
(3,N'blue-adapter','2026-09-03',0),
(4,N'Green Adapter','2026-09-01',1),
(5,N'BlueAdapter','2026-09-04',0),
(6,NULL,'2026-09-05',0),
(7,N'---','2026-09-06',0);The sample intentionally includes a label without a word boundary, an absent label, and a punctuation-only label. Those cases test what the rule should refuse to combine automatically. Store additional identity attributes where the real review needs them, such as an accepted external key or another independently verified discriminator.
Normalize Without Changing the Originals
Replace punctuation with spaces, collapse whitespace, trim the result, and lower the text. Replacing punctuation with a separator avoids joining neighboring words accidentally. A separate explicit replacement handles a nonbreaking space in this sample rule because the shorthand whitespace class is not a universal Unicode whitespace classifier.
SELECT SourceID,OriginalLabel,CreatedAt,IsVerified,
LOWER(TRIM(REGEXP_REPLACE(
REGEXP_REPLACE(REPLACE(OriginalLabel,NCHAR(160),N' '),
N'\p{P}',N' ',1,0),
N'\s+',N' ',1,0)))
COLLATE Latin1_General_100_BIN2 AS NormalizedLabel
INTO #NormalizedLabels
FROM #SourceLabels;
SELECT SourceID,OriginalLabel,NormalizedLabel
FROM #NormalizedLabels ORDER BY SourceID;The occurrence value zero replaces every match. The normalized column uses a binary collation for the subsequent grouping so the already prepared representation is compared without another layer of linguistic equivalence. The lower-case transformation still has its own collation behavior. Test that behavior with the languages in the actual data.
Regular-expression matching follows its supported pattern rules rather than SQL collation's linguistic comparison rules. Review Unicode punctuation and whitespace coverage explicitly. Do not replace the pattern with a broad remove-everything expression without checking which valid separators or symbols that wider rule would erase.
Group Near-Duplicate Rows on Meaningful Values
Group the prepared labels and retain groups with more than one row. Exclude NULL and empty normalized values from the automatic match population. Two records with no meaningful label need separate investigation, not a conclusion that their absence proves they are duplicates.
SELECT NormalizedLabel,COUNT_BIG(*) AS CandidateCopies
FROM #NormalizedLabels
WHERE NormalizedLabel IS NOT NULL AND NormalizedLabel<>N''
GROUP BY NormalizedLabel
HAVING COUNT_BIG(*)>1;Near-duplicate rows appear here only under the chosen label rule. The compact value BlueAdapter remains different from a two-word label because the normalization does not invent a missing word boundary. Changing that rule can improve one match while merging valid unrelated identifiers. Add a new rule only with accepted examples and a recorded policy version.
Rank Near-Duplicate Rows in a Fixed Order
Rank verified records before unverified ones, then older creation times, then the unique source identifier. The final identifier breaks remaining ties so repeated review produces the same proposal. That deterministic order helps workflow reproducibility; it does not turn the ranking into a factual proof of which record is correct.
WITH Ranked AS
(
SELECT *,COUNT_BIG(*) OVER (PARTITION BY NormalizedLabel) AS CandidateCopies,
ROW_NUMBER() OVER
(
PARTITION BY NormalizedLabel
ORDER BY IsVerified DESC,CreatedAt,SourceID
) AS CandidateRank
FROM #NormalizedLabels
WHERE NormalizedLabel IS NOT NULL AND NormalizedLabel<>N''
)
SELECT SourceID,OriginalLabel,NormalizedLabel,IsVerified,CreatedAt,
CandidateRank,
CASE WHEN CandidateRank=1 THEN N'Proposed survivor'
ELSE N'Review candidate' END AS ProposedRole
FROM Ranked
WHERE CandidateCopies>1
ORDER BY NormalizedLabel,CandidateRank;Inspect all copies in a group, not just the highest-ranked label. The proposed survivor can lack an attribute available in another row. A correct consolidation can therefore involve merging accepted attributes and relationships rather than deleting the lower-ranked rows unchanged.
Preserve the Evidence for an Owner Decision
Keep source identifiers, original values, normalized values, ranking inputs, and the rule version with each reviewed group. Record an explicit survivor decision and rejected matches. That evidence lets someone explain a later consolidation without reconstructing what the source looked like before normalization.
I retain intentional nonmatches as part of the review result. They prevent the next cleanup from repeatedly proposing a rejected merge under the same rule. Keep those exceptions scoped to stable identities and the relevant normalization version so they do not silently suppress a valid future investigation after the data changes.
Which independent attribute proves these records belong together? Use that question to guide the review beyond label similarity. A match on names alone is especially weak when the application allows repeated labels for different entities. Require the additional evidence the data contract needs before authorizing a write.
Plan Relationship Changes Before Any Deletion
If consolidation is approved, inventory foreign keys and application references to each source ID. Decide how accepted references move to the survivor and how conflicting attributes are resolved. Unique constraints, history requirements, and downstream consumers can make a simple DELETE an incomplete or invalid consolidation.
Use a saved before-state and a tested transaction or recovery plan for the accepted correction. Recheck current values against the reviewed values before applying it so newly changed rows are not processed under stale approval. Retain an old-to-survivor mapping where audit and downstream interpretation require it.
Do not use the row-number filter as a deletion predicate merely because it conveniently identifies lower-ranked copies. Ranking is a proposal stage. The write stage needs its own accepted population, concurrency controls, affected-row verification, and retention decision.
Validate the Rule Before Reusing It
Test punctuation-only inputs, NULLs, tabs, line breaks, Unicode marks, case variants, and labels that intentionally differ by a symbol. Confirm that original values remain intact. For larger datasets, inspect the plan and processing scope rather than repeatedly normalizing every row for an interactive screen.
Near-duplicate rows become a manageable review queue when the rule is explicit and the evidence is preserved. Keep the normalization small enough to explain and the survivor decision separate enough to challenge. That gives the cleanup a defensible purpose without letting a convenient grouping query decide the identity model.
Related reading on this blog: Finding Duplicate Customers With T-SQL and Trigram Matching: Finding Similar Words With an N-Gram Table.
A normalized match is not proof of duplication, it is a candidate relationship that needs an accepted review decision.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
