SQL split string and insert into table

Question

I am looking for some guidance for the problem below.

I have a table with two columns OP_ID and value.

The value column is string that has two delimiters in it a comma and for the end of the line a hash.

The current table is called Torque.

OP_ID	value
930823	1,0.45,0.5,0.55,N.m.#2,0.25,0.3,0.35,N.m.#
930824	1,0.45,0.5,0.55,N.m.#

And the desired output into table called Torque2

  OP_ID   | Tool | R_lower_lim | R_nominal_torque| R_Upper_lim | units
----------|------|-------------|------------------|------------|--------
  930823  | 1    | 0.45        | 0.5              | 0.55       | N.m.
  930823  | 2    | 0.25        | 0.3              | 0.35       | N.m.
  930824  | 1    | 0.45        | 0.5              | 0.55       | N.m.

I have not entered any code into the question because all of my attempts have been no where near the desired result.

Many thanks.

Which dbms are you using? (The answer will probably use product specific functions.) — jarlh, Feb 12 '21 at 21:24
Hi I am using MS SQL server management studio on version 14.0.2027.2 sql express. — jon martin, Feb 12 '21 at 21:35
Probably need a procedure that parses string to array or collection and writes records to table. I could easily do this in Access VBA but no idea how to program for SSMS. — June7, Feb 12 '21 at 21:41

Gordon Linoff · Accepted Answer · 2021-02-13T00:47:06.737

This is a big string-split and re-aggregation problem. The following assumes that row_number() over order by (select NULL)) returns the correct sequential number.

This tends to be true in practice, but Microsoft has not confirmed in the documentation that it is always true. I should reference this: Aaron Bertrand claims that this is true (here). I am uncomfortable relying on undocumented features, but in this case, the alternative is a sort that would fail if there are duplicate values.

select t.*, s.value, s2.*
from t cross apply
     string_split(t.value, '#') s cross apply
     (select max(case when seqnum = 1 then s2.value end) as Tool,
             max(case when seqnum = 2 then s2.value end) as R_lower_lim,
             max(case when seqnum = 3 then s2.value end) as R_nominal_torque,
             max(case when seqnum = 4 then s2.value end) as R_Upper_lim,
             max(case when seqnum = 5 then s2.value end) as units
      from (select s2.value,
                   row_number() over (order by (select null)) as seqnum
            from string_split(s.value, ',') s2
           ) s2
     ) s2
where s.value <> '';

Here is a db<>fiddle.

MS has documented that there is [no guarantee](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15) - _"The output rows might be in any order. The order is not guaranteed ..."_ And surely you know that your order-by-null kludge is equally problematic. — SMor, Feb 12 '21 at 23:46
@SMor . . . I added the reference and some more verbiage. I'm quite reluctant to depend on undocumented features. But the alternative seems a bit worse because it precludes duplicated values across columns. — Gordon Linoff, Feb 13 '21 at 00:47

SQL split string and insert into table

1 Answers1