Split Multiple Columns Into Multiple Columns by Delimiter

Question

I would like to split multiple columns into multiple columns by a delimiter. I am using SQLite, but I am open to importing into another DBMS, if necessary.

---

An example table, showing how the table currently is, where “;” is the delimiter:

http://sqlfiddle.com/#!5/f1845

---

An example table, showing how I’d like the updated table to look:

http://sqlfiddle.com/#!5/d6283f

Answer 1

In SQLite, you have to do it the hard way with the instr() and substr() functions:

SELECT before_semicolon,
       substr(rest, 1, pos - 1) AS after_semicolon1,
       substr(rest, pos + 1)    AS after_semicolon2
FROM (SELECT before_semicolon,
             rest,
             instr(rest || ';', ';') AS pos
      FROM (SELECT substr(data1, 1, pos - 1) AS before_semicolon,
                   substr(data1, pos + 1)    AS rest
            FROM (SELECT data1, instr(data1 || ';', ';') AS pos
                  FROM example)));

It might be a better idea to create a user-defined function like Postgres's split_part().

Answer 2

You said you are open to other DBMS solutions as well.

This is very easy in Postgres:

select id, 
       split_part(data1, ';', 1) as Data1_Before_Semicolon, 
       split_part(data1, ';', 2) as Data1_After_Semicolon1, 
       split_part(data2, ';', 1) as Data2_Before_Semicolon, 
       split_part(data2, ';', 2) as Data2_After_Semicolon1, 
       split_part(data2, ';', 3) as Data2_After_Semicolon2,
       split_part(data3, ';', 1) as Data3_Before_Semicolon, 
       split_part(data3, ';', 2) as Data3_After_Semicolon1, 
       split_part(data3, ';', 3) as Data3_After_Semicolon2,
       split_part(data4, ';', 1) as Data4_Before_Semicolon, 
       split_part(data4, ';', 2) as Data4_After_Semicolon1, 
       split_part(data4, ';', 3) as Data4_After_Semicolon2,
       split_part(data5, ';', 1) as Data5_Before_Semicolon, 
       split_part(data5, ';', 2) as Data5_After_Semicolon1, 
       split_part(data5, ';', 3) as Data5_After_Semicolon2
from example; 

Online example: https://rextester.com/RSNO8845

To update it, use the same expression:

update the table
   set Data1_Before_Semicolon = split_part(data1, ';', 1, 
       Data1_After_Semicolon1 = split_part(data1, ';', 2)
        ....

Answer 3

So this is not for SQLite so this may not be for what you are looking for and it isn't necessarily the same thing for what you are asking for.

In SQL Server this is a function that returns a single column table for each "element" of a string by a certain delimiter. I know the results come out in a vertical fashion as opposed to horizontal but maybe it helps.

CREATE FUNCTION [dbo].[fSplitVarcharString]
(
 @pVarcharString varchar(8000),
 @pDelimiter   char
)  
RETURNS @varcharArray TABLE ( varcharValue  varchar(50) )

AS
BEGIN
 DECLARE 
 @siIndex     smallint,
 @siStart     smallint,
 @siDelSize    smallint

 SET @siDelSize = LEN(@pDelimiter)
 --loop through source string and add elements to destination table array
 WHILE LEN(@pVarcharString) > 0
 BEGIN
  SET @siIndex = CHARINDEX(@pDelimiter, @pVarcharString)
  IF @siIndex = 0
  BEGIN
   INSERT INTO @varcharArray VALUES(@pVarcharString)
   BREAK
  END
  ELSE
  BEGIN
   INSERT INTO @varcharArray VALUES(SUBSTRING(@pVarcharString, 1, @siIndex - 1) )
   SET @siStart = @siIndex + @siDelSize
   SET @pVarcharString = SUBSTRING(@pVarcharString, @siStart , LEN(@pVarcharString) - @siStart + 1)
  END
 END

 RETURN
END



GO