1

Looking for solutions to my Replace() issue outlined here, I thought an alternative might be a replace within SQL when moving data to the deployment data extension.

However, I've discovered that we can't Replace a string with a semi-colon as it is a reserved word.

Replace(FIELD,"apos;","'")

Is there a work-around in SQL Query builder to get this to work?

6

There is, but it is not intuitive.

You will need to use the CHAR() function in SQL to do this as SFMC SQL Validator will not allow the reserved char ; inside of your query no matter if it is escaped or inside a string.

So for example, you would do: Replace(FIELD,'&apos' + Char(59), Char(39))

Char(59) = ;
Char(39) = '

To help remove the complexity of trying to escape a single quote inside a single quoted string in SQL, I just used the Char() function again to represent the single apostrophe.

ref(http://www.asciitable.com/)

| improve this answer | |
  • This worked! @Gortonington, add it to my tab! Some additional notes: - we have to include an "AS" statement when modifying the record (i.e., Replace(...) as Field_Name - Gortonington left out the ampersand, but check your own work people! – George Rosedale Oct 3 '19 at 21:08
  • added in the ampersand - I didn't even notice, I just copy/pasted across. My bad! – Gortonington Oct 3 '19 at 21:19

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.