Search code examples
sqlsql-servert-sql

How to find corrupt record with unicode character in SQL and delete the record

I have a table with huge sets of rows. There are a few record with a strange unicode character like: \uDB6D

Due this I get an error in my SQL report builder:

ERROR: An exception has occurred in data set 'DataSet1'. Details: System.Text.EncoderFallbackException: Unable to translate Unicode character \uDB6D at index 184 to specified code page.

I tried several queries but I am unable to find the row with the unicode. How to trace the record and eventually delete it?

These are the queries I tried:

SELECT *
FROM dbo.TestTable
WHERE SomeString LIKE N'%�%';

SELECT *
FROM dbo.TestTable
WHERE SomeString LIKE CONCAT('%',UNICODE(0xDB6D),'%');
Solution

  • charindex does the magic:

    select *
from dbo.TestTable
where charindex(nchar(0xDB6D), SomeString) > 0