Author: Paulo van Breugel
Updated on: 2012-02-14
Split a string in a column in a table in a SQL database into more columns (similar to text-to-columns in LibreOffice Calc or Excel).
The answer below is from http://www.npap.me/blog/sqlite-split-string, and allows to split a string based on a delimiter.
In this example, I use SQLite to split a string by a delimiter.
create table author (name text); insert into author (name) values ('Max Red'); insert into author (name) values ('Susan Sue'); insert into author (name) values ('John Post');
SELECT substr(name, 1, pos-1) AS first_name, substr(name, pos+1) AS last_name FROM (SELECT *, instr(name,' ') AS pos FROM author) ORDER BY first_name, last_name; -- To remove possible white spaces trim(X,Y)
+------------+-----------+ | first_name | last_name | +------------+-----------+ | John | Post | | Max | Red | | Susan | Sue | +------------+-----------+
The answer below is based on the information provided in this tutorial. For this example, let's assume we have a table KenyaWP with a column time that combines information on date and time, like: '2011/11/01 5:49:41+00'.
What we need is the SUBSTR function to extract the date and the time. The syntax is as follows:
SUBSTR(field_name,start_location) SUBSTR(field_name,start_location,substring_length )
If the start location is a positive integer X then the substring will begin X number of characters from the left of the string. If the start location is a negative integer then the substring will begin X number of characters from the right. Thus, to extract a column with only the data from the field time in the table use the following:
SELECT SUBSTR(time,1,10) FROM KenyaWP
The time is slightly more complicated as the length of the time string varies (see e.g., 9:10:26+00 vs 12:10:26+00). First step is the same as above
SELECT SUBSTR(time,12,8) FROM KenyaWP
This extracts the time, excluding the +00. Now I need to remove the excess whitespace, for which I can use the sqlite trim(X,Y) function. The trim(X,Y) function returns a string formed by removing any and all characters that appear in Y from both ends of X. If the Y argument is omitted, trim(X) removes spaces from both ends of X. I can combine this function with the one above to extract and at the same time remove white space:
SELECT TRIM(SUBSTR(time,12,8)) FROM KenyaWP
These queries just produces a temporary view of course. We need to add one new column for the data. We will use the already existing time column for the time.
ALTER TABLE KenyaWP ADD date TEXT; UPDATE KenyaWP SET date = (SELECT substr(time,1,10) FROM KenyaWP); UPDATE KenyaWP SET time = (SELECT TRIM(SUBSTR(time,12,8)) FROM KenyaWP);
If you want to keep the original 'time' column, or want to change the order of the columns, you'll need to create a new table and fill those with the original and new values:
BEGIN TRANSACTION; CREATE TABLE tmp (PKUID INTEGER, Geometry MULTIPOINT, WP TEXT, date_time TEXT, date TEXT, TIME TEXT, Elevation REAL Notes TEXT); INSERT INTO tmp (PKUID, Geometry, WP,date_time, Elevation, date, TIME, DESC) SELECT PKUID, Geometry, WP, TIME, elevation, substr(TIME,1,10), TRIM(SUBSTR(TIME,12,8)), DESC FROM KenyaWP; DROP TABLE KenyaWP; ALTER TABLE tmp RENAME TO KenyaWP; COMMIT;
If you have questions
If you have questions or comments about the text, let me know. You can use this contact form. Please make sure to include the page title ("SQLite: split strings") or page name ("split strings").