Two names look identical in the results grid, yet a join misses one of them. Hidden characters can survive a visual check and turn ordinary equality into a data-cleanup problem.
Measure the Bytes First
DATALENGTH reports bytes stored, while LEN ignores trailing spaces in many cases. Compare both for suspicious values, along with a binary representation when needed. An nvarchar value uses Unicode storage, so bytes and visible characters are different concepts. I put the two candidate values side by side with their source keys before updating anything. A DISTINCT result that contains both is a clue, but collation behavior can make some characters compare equal and others unequal. The exact collation belongs in the investigation.
DECLARE @a nvarchar(100) = N'North' + NCHAR(32) + N'Bay';
DECLARE @b nvarchar(100) = N'North' + NCHAR(160) + N'Bay';
SELECT @a AS first_value, DATALENGTH(@a) AS first_bytes,
@b AS second_value, DATALENGTH(@b) AS second_bytes,
CASE WHEN @a = @b THEN 1 ELSE 0 END AS equals_here;List Hidden Characters by Code Point
Walk the string by position and print UNICODE for each character. That exposes a nonbreaking space, tab, carriage return, or zero-width code point that the grid hides. For supplementary Unicode characters, a UTF-16 code-unit walk needs care because one visible character can use a surrogate pair. For the common invisible-character cleanup, the code-unit list is a practical first pass. I focus on the positions where the two values differ and confirm them against the source system.
DECLARE @value nvarchar(100) = N'North' + NCHAR(160) + N'Bay';
WITH positions AS
(
SELECT 1 AS position_number
UNION ALL
SELECT position_number + 1
FROM positions
WHERE position_number < DATALENGTH(@value) / 2
)
SELECT position_number,
SUBSTRING(@value, position_number, 1) AS character_value,
UNICODE(SUBSTRING(@value, position_number, 1)) AS unicode_value
FROM positions
OPTION (MAXRECURSION 0);Name the Common Hidden Characters
A normal space is code point 32. A nonbreaking space is 160. Tabs and carriage returns are 9 and 13. Zero-width characters include code points that do not appear in the grid at all. Copying text from documents, web pages, and imports is a common route. I check source files before blaming SQL Server. If the import code introduced the character, cleaning stored rows without fixing the importer only schedules the same incident again.
Do not strip every non-ASCII character. Names legitimately contain accents and scripts beyond basic Latin letters. A safe rule targets the exact unwanted codes and preserves meaningful text.
Clean With an Explicit Mapping
TRIM removes ordinary leading and trailing spaces by default. It does not magically normalize every Unicode whitespace character inside a name. Use REPLACE for the hidden characters you identified, then TRIM where the business rule wants edge spaces gone. The sample value below starts with a tab and ends with a space, and the cleaned result comes back as a plain North Bay. Stage the cleaned value, compare before and after, and review collisions. Two distinct customer records can become the same normalized name; that does not mean they should be merged. I keep the original value in an audit column or export until the change is accepted.
DECLARE @value nvarchar(100) = NCHAR(9) + N'North' + NCHAR(160) + N'Bay ';
SELECT TRIM(REPLACE(REPLACE(@value, NCHAR(160), N' '), NCHAR(9), N' ')) AS cleaned_value;
Block Known Hidden Characters With a Constraint
Add validation at ingestion and a CHECK constraint for codes the domain forbids. Use a binary collation in the check when collation equivalence could blur the character distinction. Test the constraint with both accepted and rejected examples. In the script below, the second INSERT is meant to fail with error 547, which proves the rule works. A broad pattern such as "ASCII only" can reject valid names and addresses, so keep the rule narrow. If zero-width marks are meaningful in some language or field, do not ban them globally.
CREATE TABLE #CleanNames
(
CustomerID int NOT NULL PRIMARY KEY,
CustomerName nvarchar(100) NOT NULL,
CONSTRAINT CK_CleanNames_NoNbsp
CHECK (CHARINDEX(NCHAR(160),
CustomerName COLLATE Latin1_General_100_BIN2) = 0)
);
INSERT #CleanNames VALUES (1, N'North Bay');
INSERT #CleanNames VALUES (2, N'North' + NCHAR(160) + N'Bay');What should happen to a rejected import row? Put it in a review queue with the source key and code point. A clear error is better than silently editing a legal name into a different one.
Check Collation Before You Normalize
SQL Server collations can treat case, accents, width, and some whitespace characters differently. A value comparison under the application collation can produce a different answer from a binary code-point comparison. I run both when a join or DISTINCT result is surprising. The binary comparison tells me whether stored characters differ; the application comparison tells me whether the query regards them as equal. Neither alone decides which value the business wants. A name that contains a nonbreaking space can be a data-entry error, while a character outside ASCII can be entirely valid.
Choose one normalization rule for each field. Customer display names, postal codes, and product IDs have different contracts. Replacing every unusual character with a space is not a universal cleanup policy. I keep a sample of affected rows and source systems for owner review before any update.
Make Cleanup Repeatable and Reversible
Write a SELECT that shows original value, cleaned candidate, and source key. Count collisions after cleanup, then update in small batches under a reviewed transaction plan. Keep an audit copy of original bytes. I use a binary collation or UNICODE-based check to target the exact unwanted code points; a broad LIKE pattern can match more than intended under some collations. Test the CHECK constraint against expected international names and codes so it does not reject legitimate input.
What if two distinct records normalize to the same indexed value? Stop and review the merge rule with the data owner. A unique constraint failure is useful evidence that the proposed cleanup changes identity. After the update, compare join results and DISTINCT groups to the baseline, and monitor new imports. A one-time fix is complete only when both existing rows and the source path have been addressed. Otherwise the same invisible character returns with the next file.
Use a binary comparison when the display grid and ordinary equality disagree. The exact code point is more useful than another screenshot of two identical-looking strings. I include the source row key and import batch in the cleanup report. That makes it possible to fix the source feed and to prove which stored rows were changed.
Recheck Joins and Uniqueness
After cleanup, rerun the failed join and duplicate checks under the application's actual collation. Confirm that counts and keys reconcile. Check indexes or unique constraints that use the column; normalized values can create collisions. I run the same check on new rows after the importer fix to make sure the problem stopped at the source. A one-time UPDATE is not complete if tomorrow's file brings the same invisible mark.
Keep a short list of recognized problem codes with the data contract. The next DBA should not have to discover a nonbreaking space by staring at two identical-looking grid cells.
Related reading on this blog: Tips from the SQL Development Series: Wildcard: Querying Special Characters: Day 2 of 35 and Change Database and Table Collation.
A matching-looking string is not matching data, it is only matching typography.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
