CODEWITHSUNDEEP
sqlsql-serverencoding
MoonLightFlower
MoonLightFlower

Reputation: 183

SQL Server - Retrieve data with wrong encoding

I have a SQL Server which contains data with accents and sometimes with wrong encoding(Data with the character �). I was wondering if there is a way to retrieve every line in this table which contains the character �

I have try the like function

SELECT 
BadField
FROM Table1
WHERE BadField LIKE N'%�%'

and

SELECT 
BadField
FROM Table1
WHERE BadField LIKE '%'+NCHAR(0xfffd)+ '%'

And with Regexp Code

SELECT 
BadField
FROM Table1
WHERE BadField '%[^A-Za-z0-9, ]%'

But i didn't manage to get the lines with the replacement character �.

Upvotes: 1

Views: 725

Answers (1)

Thom A
Thom A

Reputation: 95949

Try using a binary collation. This, at least, works for some made up valuse:

SELECT *
FROM (VALUES(N'abc'),(N'�'),(N'a�c'))V(S)
WHERE V.S COLLATE Latin1_General_BIN LIKE N'%�%';

Which returns both '�' and 'a�c'.

Upvotes: 3

Related Questions