0

I'm trying to replace double quotes in "SMTPMessage" field of Bounce dataview in Marketing Cloud. But I've entered in several ways, but the quotes do not disappear. What I did so far is this query.

SELECT
AccountID,
OYBAccountID,
JobID,
ListID,
BatchID,
SubscriberID,
SubscriberKey,
EventDate,
IsUnique,
Domain,
BounceCategoryID,
BounceCategory,
BounceSubcategoryID,
BounceSubcategory,
BounceTypeID,
BounceType,
SMTPBounceReason,
SMTPMessage,
REPLACE(REPLACE(REPLACE(SMTPMessage,'{',' '),'}',' '),''"'',' ') AS SMTP_Message,
SMTPCode,   
TriggererSendDefinitionObjectID,
TriggeredSendCustomerKey

FROM _bounce

enter image description here

1
  • 2
    why do you have two sets on single quotes around your double quote?
    – EazyE
    Feb 22, 2022 at 16:18

1 Answer 1

1

The three fields SMTPBounceReason, SMTPMessage and SMTPCode in the bounce data view are usual suspects to break your sql, because they can contain completely random stuff, including "A joke line entered by some company mail server's admin. He just thought it was funny to have a special bounce message returned."

Great fun.

Trim them to 4000 chars, replace all double quotes with single quotes, and also replace commas (I also included semicolons) with "-" to be sure.

/* replace commas and semicolons with -, double quotes with single quotes */
, REPLACE(REPLACE(REPLACE(LEFT(SMTPBounceReason, 4000), CHAR(44),'-'), CHAR(34),CHAR(39)), CHAR(59),'-') AS SMTPBounceReason
, REPLACE(REPLACE(REPLACE(LEFT(SMTPMessage, 4000), CHAR(44),'-'), CHAR(34),CHAR(39)), CHAR(59),'-') AS SMTPMessage
, REPLACE(REPLACE(REPLACE(SMTPCode, CHAR(44),'-'), CHAR(34),CHAR(39)) , CHAR(59),'-') AS SMTPCode

reference for all these chars: https://theasciicode.com.ar/ascii-printable-characters/semicolon-ascii-code-59.html

You must log in to answer this question.

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