RaqForum 41 No.
44 View  • 
     ...

SQL: Split a string with an uncertain number of segments into multiple columns

 

A field in a table of the MS SQL database is a comma separated string with an indefinite number of strings.

A_NUMBER

items

1

i1,i2,i3,i4,i5,i6

2

j1,j2,j3,j4,j5,i6,i7,i8

3

k1,k2,k3

4

5

m1

Now we need to split this field into 5 new fields, named Srllno1 to Srllno5, with values of the 1st to 4th string and the 5th to last string after splitting the original field. Fill in null when the number of strings is insufficient.

Srllno1

Srllno2

Srllno3

Srllno4

Srllno5

i1

i2

i3

i4

i5,i6

j1

j2

j3

j4

j5,i6,i7,i8

k1

k2

k3






m1



SPL code:

　A

1

=mssql.query("select items from tb”)

2

=A2.(items.split@c())

3

=A3.new(~.m(1):Srllno1, ~.m(2):Srllno2, ~.m(3):Srllno3, ~.m(4):Srllno4, ~.m(5:).concat@c():Srllno5)

A1： Query the database through JDBC and retrieve the items field.

A2： Split the items field into multiple strings using commas.

A3： Create a new two-dimensional table, get the first to fourth parts of the A2 current member, and name them as the new fields Srllno1 to Srllno4; Get the 5th to the last part and merge them to name the new field Srllno5. The m function can flexibly get members by position and automatically handle array out of bounds.

Problem source：https://stackoverflow.com/questions/78384942/converting-nth-delimiter-in-csv-string-into-columns


 
 •  44 View  •