0

I am trying to concatenate BLOCK_ID1, BLOCK_ID2 and BLOCK_ID3 into one string in BLOCK_ID. I want to remove the last comma in the string.

MyDE

UNIQUE_ID BLOCK_ID1 BLOCK_ID2 BLOCK_ID3
A 1 2 3
B 1 2
C 1

When i do:

SELECT UNIQUE_ID,
CONCAT(BLOCK_ID1,', ', BLOCK_ID2, ', ', BLOCK_ID3) as BLOCK_ID
From MyDE

OUTPUT DE:

UNIQUE_ID BLOCK_ID
A 1, 2, 3
B 1, 2,
C 1,

I want to remove the last comma from B and C

1
  • 2
    hi, that's something you can just google, you just have to know the kind of SQL the marketing cloud uses - it's t-sql / Transact-sql. I did that just last week googling for t-sql remove last character comma and the solution here helped me: social.msdn.microsoft.com/Forums/sqlserver/en-US/… Oct 26, 2021 at 10:54

1 Answer 1

0

Using the information that Marketing Cloud uses T-sql from Jonas Lamberty's answer, I figured a way to solve my problem.

Here is the code:

SELECT 
UNIQUE_ID,
CONCAT(BLOCK_ID1 + CASE WHEN BLOCK_ID2 = '' then '' else ', ' end,
BLOCK_ID2 + CASE WHEN BLOCK_ID3 = '' then '' else ', ' end,
BLOCK_ID3

) as BLOCK_ID

From myDE

You must log in to answer this question.

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