ETL - Get/Remove Values after First Space

TimWells

Hi All,

I am trying to separate the string's below to form two different columns.

So my current column containing all the value is called 'Type' and i want to separate it to be two columns 'Medium' and 'Campaign'

'paid Car_Loans' should be 'paid' and 'Car Loans'

'callext Car Loans 2021' should be 'callext' and 'Car Loans 2021'

Is there anyway to do this in an ETL?

MarkSnodgrass

Yes, you can do this very easily in Magic ETL using the Split Columns tile. Just drag the Split Columns tile in, select that column you wish to split, choose Whitespace for what to split on, place a check next to "keep extra splits", then give a name to the column that will hold the values before the space, click add column to give a name to the column that will hold the values after the space.

If you have Magic ETL 2.0, you can use the Add Formula tile and use the SPLIT_PART function to separate them like this:

First column: SPLIT_PART(`fieldname`,' ',1)

Second column: TRIM(SUBSTRING(`fieldname`,INSTR(`fieldname`,' '),LENGTH(`fieldname`)))

The second one is a bit more involved because you want everything after the space, not just to the next space.

MarkSnodgrass

Yes, you can do this very easily in Magic ETL using the Split Columns tile. Just drag the Split Columns tile in, select that column you wish to split, choose Whitespace for what to split on, place a check next to "keep extra splits", then give a name to the column that will hold the values before the space, click add column to give a name to the column that will hold the values after the space.

If you have Magic ETL 2.0, you can use the Add Formula tile and use the SPLIT_PART function to separate them like this:

First column: SPLIT_PART(`fieldname`,' ',1)

Second column: TRIM(SUBSTRING(`fieldname`,INSTR(`fieldname`,' '),LENGTH(`fieldname`)))

The second one is a bit more involved because you want everything after the space, not just to the next space.

TimWells

Thanks @MarkSnodgrass! That Easy.