SQL: extract items from list to put onto new row

Question

Is there a way to extract out list items within a column and put each item in its own row? I also want to do away with the list brackets [] too! Can anyone help?

Input:

name    doc_id                              type
JD      [409839589143224]                   1843
JD      [470573363064028,239564999464566]    778
BK      [426580780775177,342730259144025]    202

Desired Output:

name    doc_id                              type
JD      409839589143224                     1843
JD      470573363064028                     778
JD      239564999464566                     778
BK      426580780775177                     202
BK      342730259144025                     202

quite a mess you have. is there a limit on the number of individual `doc_id`s in a given tuple? I would say it may be easier to do this programmatically from Python or some other scripting language or to pump it into Postgre and do what @jpw said. — Brad Allred, Nov 14 '13 at 15:44

score 0 · Answer 1 · edited May 23 '17 at 12:19

this is solution using tsql, i`m sure you can do the same with MySql

CREATE FUNCTION ufn_split (@list nvarchar(MAX))
   RETURNS @tbl TABLE (part nvarchar(max) NOT NULL) AS
BEGIN
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      INSERT @tbl (part)
         VALUES (substring(@list, @pos + 1, @valuelen))
      SELECT @pos = @nextpos
   END
   RETURN
END;
GO

SELECT t.name, st.part, t.type 
FROM @table AS t
CROSS APPLY ufn_split( LEFT(RIGHT(t.doc_id, LEN(t.doc_id) - 1),LEN(t.doc_id) - 2) ) AS st;

The first function is implimentation of Split function on TSQL from here Split function equivalent in T-SQL?

SQL: extract items from list to put onto new row

1 Answers1