Today I learned SQL Server’s default encoding automatically equates similar characters to each other for sorting/searching. Normally that’s a good thing (e.g. so c and ç sort the same way) but today I wanted to find a specific unicode character…
I needed to find (and replace) all “Narrow Non-Break Space” characters but searching for
LIKE '%' + NCHAR(8239) + '%' was also finding ordinary spaces.
The answer was to tell it to use a binary comparison when evaluating that field with
SELECT * FROM Comments WHERE Comment COLLATE Latin1_General_BIN LIKE '%' + NCHAR(8239) + '%'
to find them and:
UPDATE Comments SET Comment = REPLACE( Comment COLLATE Latin1_General_BIN, NCHAR(8239) COLLATE Latin1_General_BIN, ' ' )
to replace them with regular spaces.