How to delete emojis using SQL in Marketing Cloud

Question

I have a Data Extension named "Jobs", where I keep records from dataview Job. My team started to use emojis in subject lines of the e-mails, so also in my table "Jobs", I have those emojis, e.g. 😍, 💵, 😎.

I have also another system, which retrieves data from my table "Jobs", using SOAP API. Unfortunately, this tool cannot handle emojis and entire process is failing. I cannot modify this another system, so I want to delete those emojis from my data. But I don't know how to do that.

Using following query:

SELECT 
  UNICODE(SUBSTRING(Subject, 1, 1)) AS Char_first,
  UNICODE(SUBSTRING(Subject, 2, 1)) AS Char_second 
FROM Jobs 

I was able to check, that e.g. when this emoji 😍 is at the beginning fo the subject, the first Unicode code is 55357 and the second one is 56845. So I tried folowing queries:

SELECT
  Subject,
  REPLACE(Subject, CHAR(55357), '') AS cleaned_subject,
FROM Jobs


SELECT
  Subject,
  REPLACE(Subject, CHAR(55357) + CHAR(56845), '') AS cleaned_subject,
FROM Jobs


SELECT
  Subject,
  REPLACE(TRIM(Subject), CHAR(55357) + CHAR(56845), '') AS cleaned_subject,
FROM Jobs

But none of them works correctly. Always column "Subject" is correctly retrieved, but column "cleaned_subject" is always empty.

Answer 1

You can try something like this:

SELECT
    subject,
    REPLACE(CAST(subject as char(255)), '??', '' ) AS cleaned_subject
FROM
[Jobs]

In the query above, I transform the data in "subject" column to a character string with a length of 255. The CAST() function is unable to convert the emoji so it will turn it into "??" value.

I notice that the emoji (always?) has length is 2 and cannot convert.

The last thing is similar like what you tried, I remove it by replacing the question mark value by an empty string ''

My output:

Answer 2

The problem was different. Not exactly with emojis, but somehow in SMS messages there were new line characters - CHAR(10) and CHAR(13). I'm replacing them with space - CHAR(32).

REPLACE(
    REPLACE(
        REPLACE(MessageText, CHAR(13) + CHAR(10), CHAR(32)),
        CHAR(13),
        CHAR(32)
    ),
    CHAR(10),
    CHAR(32)
) AS MessageText