Marketing Cloud - Remove Trailing Char0x00 via SQL

Question

I have been trying to create a SQL Query that would remove the trailing 0x00.

Here is the sample data:

I tried following the steps from https://stackoverflow.com/questions/43409279/sql-server-removing-0x00-characters-from-a-string and https://blogs.visigo.com/chriscoulson/removing-null-characters-0x00-from-values-in-sql-server/ but nothing has worked so far.

Has anyone successfully done this before in Marketing Cloud?

Answer 1

If that specific part is all you need to remove, you can do a simple replace rather than trying to do some complex thing.

For instance, say you have:

MyID  |  Email
01234 | [email protected]<0x00>

You can do the following to remove it:

SELECT MyID,
LTRIM(RTRIM(REPLACE(Email,'<0x00>','')))
FROM [myDE]

This will remove the <0x00> by replacing it with an empty string and then the LTRIM and RTRIM will make sure to remove any leading or trailing spaces (TRIM() is for some reason not accepted in SFMC SQL). This should then clean up your data and remove the unwanted characters and spaces.

It would output:

MyID  |  Email
01234 | [email protected]