Split Multiple Columns Into Multiple Columns by Delimiter

sqlite

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

Best Answer

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

Related Question