DEV Community

Judy
Judy

Posted on

3 1 1 1 1

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.

Image description

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.

Image description

SPL code:

Image description

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

Heroku

Simplify your DevOps and maximize your time.

Since 2007, Heroku has been the go-to platform for developers as it monitors uptime, performance, and infrastructure concerns, allowing you to focus on writing code.

Learn More

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more