splitting a dynamic ";" string into several columns?

[#1]

Hi,

Can you please tell me what is the most efficient way of getting a column which is a string consisting of several variables seperated by ";" (please see below) to split into several columns (one column for each variable between the ";"'s)

Needs to be done in the load script.

(Load script)

SQL select Date, ColumnToSplit from MyTable;

(Result Now)

Date: 01.01.2001

ColumnToSplit: 4682.50;2;1451481191401;165017;4689.47399965;true;

(Result Needs to Be)

Date: 01.01.2001

Column1: 4682.50

Column2: 2

Column3: 1451481191401

Column4: 165017

Column5: 4689.47399965

Column6: true

The lenght of the numbers can be different from a line to line and the last word also (sometimes its true, sometimes its false or sometimes its missing...

Thanks in advance for your help, much appretiated.

[sunny_talwar · Accepted solution]

May be this:

Table:

LOAD Date,

          SubField(ColumnToSplit, ';', 1) as Column1,

          SubField(ColumnToSplit, ';', 2) as Column2,

          SubField(ColumnToSplit, ';', 3) as Column3,

          SubField(ColumnToSplit, ';', 4) as Column4,

          SubField(ColumnToSplit, ';', 5) as Column5,

          SubField(ColumnToSplit, ';', 6) as Column6

SQL SELECT Date,

                     ColumnToSplit

FROM MyTable;

[#3]

Great, thanks!

[AsmJuv2004]

Hi @sunny_talwar  

How do I make this dynamic. I need to do the same thing but my output column number  will be different for each row.

[MarcoWedel]

Hi,

maybe like this?

Generic
LOAD RecNo() as ID,
'Column'&IterNo(),
SubField(ColumnToSplit,';',IterNo())
Inline [
ColumnToSplit
4682.50;2;1451481191401;165017;4689.47399965;true;
4683.50;3;1451481191402;165018;4690.47399966;false;
4684.50;4;1451481191403;165019;4691.47399967;true;
4685.50;5;1451481191404;165020;4692.47399968;false;
] While IterNo()<=SubStringCount(ColumnToSplit,';')+1;