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?

Improve this question

1 Answer 1

Reset to default
7

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
2
  • 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
    Commented Oct 3, 2019 at 21:08
  • added in the ampersand - I didn't even notice, I just copy/pasted across. My bad!
    – Gortonington
    Commented Oct 3, 2019 at 21:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .