0

Is it possible to have '&' character as an attribute value in the data extension? I am using SQL Query activity to insert the value.

Or is there a better way then my current SQL code. I've tried using the actual '&' and the ascii character like below. I realise that it is not possible to have the ':' in the SQL code.

select
case
        when B.Contact_Email is not null 
            then STUFF (
                (select ' ' + CHAR(38) + ' ' + A.Contact_First_Name
                from [some DE] A
                where A.Contact_Email = B.Contact_Email and A.OpportunityId = B.OpportunityId
                group by A.OpportunityId
                FOR XML PATH('')),1,7,'')
        else B.Contact_First_Name
    end as 'Contact_First_Name',
from
   [Some DE]

Is there another method that I can use? The purpose of this is to merge the identical email rows into one row.

Thank you all.

1 Answer 1

1

The FOR XML translates & to &. This works as intended as XML does not support the & character.

You could use a placeholder for the delimiter such as pipe (|). And then use the REPLACE function to replace the placeholder with the & character.

select
case
        when B.Contact_Email is not null 
            then REPLACE(STUFF (
                (select ' | ' + A.Contact_First_Name
                from [some DE] A
                where A.Contact_Email = B.Contact_Email and A.OpportunityId = B.OpportunityId
                group by A.OpportunityId
                FOR XML PATH('')),1,3,''), '|', '&')
        else B.Contact_First_Name
    end as 'Contact_First_Name',
from
   [Some DE]
7
  • Thanks! I forgot to mentioned that I want to avoid having "&" HTML code inserted in the data extension value. Is there a way to override this? The code above will put the value as "firstname & firstname" as a value
    – Yopz
    Aug 12, 2019 at 4:00
  • So if you write SELECT a.FirstName + ' & ' + b.firstName FROM TableA a, TableB b it saves it as firstname & firstname? When I run this query, the attribute value gets saved as firstname & firstname Aug 12, 2019 at 7:52
  • Yes that's what I got firstname & firstname. Perhaps is it because of the STUFF that turns it into XML prior to loading to Data Extension?
    – Yopz
    Aug 13, 2019 at 1:10
  • Did some quick testing. And as you say, the FOR XML rewrites & to &. I am not sure what you want to achieve with the STUFF function here. Try to replace it with the REPLACE function. Where you replace '&' with '&'. Aug 13, 2019 at 7:42
  • Thanks for your help thus far. The purpose of the STUFF function is to combine the contact first name together whenever 2 contact records have the same email address in the one opportunity. At the very end of the query, I have a partition by function to give me the row number if indeed there are duplicate emails. I have not find another SQL function that allows me to do this. Unfortunately though the SQL Query editor will prevent me from typing ";" as it is a reserved character. Perhaps there is another function similar to STUFF that doesn't require me to use FOR XML?
    – Yopz
    Aug 14, 2019 at 6:47

You must log in to answer this question.

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