0

I am importing a file data which cannot be edited from the source. I am trying to modify and extract the only needed information via Marketing Cloud SQL.

Below is the original file field data and the desired outcome.

Original Field Desired Outcome Field 1 Desired Outcome Field 2
ACCESSORIES, Categories:Women, Type_en:Accessories, Brand:Example, NECKLACE, Collection:123 Women Accessories
Categories:Men, SHORT SLEEVES, Type_en:T-shirts, CLOTHING, Collection, 132, Brand:Example2 Men T-shirts

For Desired Outcome Field 1, I want to only get the value that begins with "Categories:" and end before the comma; similar for Desired Outcome Field 2, I want to only retrieve the value that begins with "Type_en:" and end before the comma.

Is it possible to do so within Marketing Cloud SQL?

Thanks a ton in advance!

1 Answer 1

1

You can use Cross Apply function to parse the delimited field values.

SELECT
    a.Original_Field,
    SUBSTRING(a.Original_Field, b.categories_index, CHARINDEX(',', a.Original_Field, b.categories_index)- b.categories_index) AS categories,
    SUBSTRING(a.Original_Field, b.type_en_index, CHARINDEX(',', a.Original_Field, b.type_en_index)- b.type_en_index) AS type_en
FROM
    DE AS a
CROSS APPLY
    (SELECT
        NULLIF(CHARINDEX('Categories:', a.Original_Field) + 11, 11) AS categories_index,
        NULLIF(CHARINDEX('Type_en:', a.Original_Field)+ 8, 8) AS type_en_index
    ) AS b

Output:

Original_Field categories type_en
ACCESSORIES, Categories:Women, Type_en:Accessories, Brand:Example, NECKLACE, Collection:123 Women Accessories
Categories:Men, SHORT SLEEVES, Type_en:T-shirts, CLOTHING, Collection, 132, Brand:Example2 Men T-shirts

Try this: https://dbfiddle.uk/E8_lzeUK

You may want to read these articles since I just learned the function from there:

3
  • Thanks a lot Duc! Also appreciate the article sharing :) It worked!
    – Hannah Kim
    Jul 3, 2023 at 12:14
  • @HannahKim great to hear that. Can you mark it as an accepted answer then?
    – Duc Le
    Jul 3, 2023 at 13:24
  • Thought I did earlier - just did it now! Thanks again!
    – Hannah Kim
    Jul 5, 2023 at 3:59

You must log in to answer this question.

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