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

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

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.

SPL code:

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.

Open source SPL source address

Free Download