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:


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


3 Answers 3


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,
             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().


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)
  • Thanks CL., Kirk Saunders, and a_horse_with_no_name. I think I will try PostgreSQL. But, my aim is to update the table, not select. Could you give a rextester example of split_part with update?
    – Xa1
    Jun 30, 2019 at 8:04
  • @Xa1: just use those expressions in an update statement
    – user1822
    Jun 30, 2019 at 8:17
  • Okay, so: "UPDATE example SET Data1_Before_Semicolon = split_part(data1, ';', 1), Data1_After_Semicolon1 = split_part(data1, ';', 2) ..." ? What about, "select id,", from your example? Is that necessary?
    – Xa1
    Jun 30, 2019 at 8:53
  • @Xa1 no, a SELECT is not obviously not "necessary" for an UPDATE
    – user1822
    Jun 30, 2019 at 12:39
  • Thanks, I suppose it was a basic question. I have a lot to learn. I do know that a SELECT is not necessary for an UPDATE, in general, but it can be, as in: “Col = (SELECT Col FROM table2 WHERE ID = table1.ID),”. So, I wanted to make sure I correctly adapted your example.
    – Xa1
    Jul 1, 2019 at 1:23

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) )

 @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
  SET @siIndex = CHARINDEX(@pDelimiter, @pVarcharString)
  IF @siIndex = 0
   INSERT INTO @varcharArray VALUES(@pVarcharString)
   INSERT INTO @varcharArray VALUES(SUBSTRING(@pVarcharString, 1, @siIndex - 1) )
   SET @siStart = @siIndex + @siDelSize
   SET @pVarcharString = SUBSTRING(@pVarcharString, @siStart , LEN(@pVarcharString) - @siStart + 1)



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.