Split a field using 2 delimiters

[#1]

Hi,

I have a following table:

EmpID, Val

100, a-1/b-2

200, e-3/f-4/g-5

I want to split the Column 'Val' using delimiters '-' and '/' into Multiple columns to give the following output:

EmpID, Val, Split1, Split2, Split3, Split4, Split5, Split6

100, a-1/b-2, a, 1, b, 2

200, e-3/f-4/g-5, e,3,f, 4, g, 5

Here, the new columns added, Split*, are dynamic as I'm not sure of the number of splits that are available in each column value.

Can someone please help? Thanks in advance.

[marcus_sommer · Accepted solution]

Try this:

t1:

Load EmpID, Val, subfield(replace(Val, '-', '/'), '/') as ValSplit From xyz;

Subfield() will create for each value a (new) row. This is a stream-output and not a crosstable-output but often this is a better solution.

- Marcus

[#3]

Awesome!!! Thanks, Marcus. This was so simple