0

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.

0

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:

enter image description here

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.

enter image description here

| improve this answer | |
  • Worked... Thanks Naveen! – Swati Mishra May 13 at 8:31
  • You are welcome @smsy31! – Naveen VM May 13 at 9:12
  • The pair of you are working the system... I get it. – Macca May 13 at 14:24
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
| improve this answer | |
  • let me try, as my first name also has other valid characters like Æ – Swati Mishra May 12 at 11:07
  • This is not working, it is just returning all the names with special characters – Swati Mishra May 12 at 11:34
  • I'll update to search specifically for the unicode replacement character, "�", if you like. – Macca May 13 at 0:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.