substr - Sqlite Create new columns from column containing ;

Question

I have a database test.db with the first column containing 123456;abcdef;ghijk etc. Is it possible to split the data into its own colums?

123456 never changes length.

abcdef and ghijk change length and also may contain nothing.

I have tried the below string but i get the ; appear in either t2 or t3 depending on the length of abcdef and ghijk.

select substr(column,1,6) AS "t1",
       substr(column,8,6) AS "t2",
       substr(column,15,10) AS "t3" test.db

Is the ; seperator causing the issue?

Or can i output the database to .sql, format the txt then import to a new database?

Answer 1

There is no built-in SQLite function that can split strings like this.

If your are using the SQLite C API or a wrapper like APSW, you could create your own function (C, APSW).

If you want to do nothing more than a one-time conversion, export/import through a text file would be the simplest solution.