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.
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.
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