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

user_500548 0
Viewed 281

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),'%');
tsql sql-server sql
3 Answers

charindex does the magic:

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

Why don't you try to find just "DB6D" string? I just think simple.

SELECT *
FROM OrderLines
WHERE OrderLines.ItemDescription LIKE '%DB6D%'
user_125082490

Or you can try this:

SELECT *
FROM OrderLines
WHERE OrderLines.ItemDescription LIKE '%' + nchar(56173) + '%'
user_125082490
Related