Checking for a non ascii character in SQL Query activity

Question

I have certain records in my DE, where the first name contains '�'. I wish to extract such records. Obviously a like wouldn't work. Is there a way to do this in SQL Query Activity.

Answer 1

I tried to reproduce your issue in my instance.

You can see one row is fine but the other row has has special characters as per the below screenshot:

You can use the below SQL Query :

Select [Template Identifier],Country,[Reservation Text],[Booking Text] 
from [Test Dynamic Text]
where [Booking Text] <> CONVERT(varchar, [Booking Text])

At the resultant data extension am getting only one row which has the special characters. Let me know if this helps.

Answer 2

You can find those records containing multi-byte characters by converting the First Name field to a single-byte data type and then comparing against the First Name for equality:

SELECT 
    FirstName
FROM 
    MyDataExtension
WHERE
    FirstName != cast(FirstName as varchar(1000))

If you're specifically interested in locating records that contain the unicode replacement character, '�', then narrow down your comparison to just this one character code:

SELECT 
    FirstName
FROM 
    MyDataExtension
WHERE
    CHARINDEX(NCHAR(65533) COLLATE Latin1_General_BIN,FirstName) > 0