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.