Marketing Cloud SQL String Formatting to Retrieve Only Part of Data

Question

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!

Answer 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:

• Parsing Delimited Fields Values with SQL Cross Apply
• Splitting a string of comma-separated values in a Data Extension field using SQL