SQLite split string

15/10/2013

In this example, I use SQLite to split a string by a delimiter.

Schema

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

Query

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;

Result

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| John | Post |
| Max | Red |
| Susan | Sue |
+------------+-----------+

Try online at SQL Fiddle.

14 Comments

Paulo link

28/5/2015 07:19:24

Brilliant, looked for a lot of solutions, but this beats them all in terms of functionality and simplicity!

yodann link

6/9/2015 08:09:13

thank u. it's amazing!

Alice

4/4/2016 09:29:25

I love you!

scientist

29/7/2016 10:31:17

Great! God bless you

Paulo link

14/9/2016 22:27:09

What if I want to update a column (a third column in the same table for example) with the results. Something like:

UPDATE author
SET XTRCOL = (SELECT substr(name, 1, pos-1)
FROM (SELECT *, instr(name,' ') AS pos
FROM author))

However, this updates the column with the first value, i.a.w. it writes 'John' to all rows in the column XTRCOL

Nikos Papandreou

13/11/2017 15:14:19

Paulo, you forgot to add the reference for each set

UPDATE author
SET name = (SELECT substr(name, 1, pos-1)
FROM (SELECT *, instr(name,' ') AS pos
FROM author)
where name=author.name);

Piyush Chakrawarty

12/9/2018 08:06:23

I am having the same problem even after putting "where name=author.name" , output is same as before i.e. all rows getting updated with first value only. Any other way to solve the problem.

Tom link

13/11/2017 12:29:55

Great!!! But... What, if there are more than two elements to split?

Nikos Papandreou

13/11/2017 15:14:38

Just add one more nested split!

insert into author (name) values ('Wernher von Braun');

SELECT first_name,
substr(last_name, 1, pos-1) AS middle_name,
substr(last_name, pos+1) AS last_name
FROM(
SELECT first_name,
last_name,
instr(last_name, ' ') AS pos FROM
(
SELECT substr(name, 1, pos-1) AS first_name,
substr(name, pos+1) AS last_name
FROM
(SELECT *,
instr(name,' ') AS pos
FROM author)
)
)

Tomek link

13/11/2017 15:53:36

Thank you! ;)

Gernot B

4/7/2018 14:37:18

Great solution. Is there a possibility to split the string into columns if you do not know how many substrings you have?

I have a field with values like:
1) Product odour abnormal (n); 2) Product taste abnormal (n); 3) Poor quality drug administered (n);
1) Faeces discoloured (n);
1) Asthenia (n); 2) Weight decreased (n); 3) Thirst (n); 4) Insomnia (n); 5) Malaise (n); 6) Abdominal distension (n); 7) Abdominal discomfort (n); 8) Expired product administered (n);

Unfortunately I have no influence on the party providing these data - I know they are bad

DonkeyKong

6/8/2021 17:24:06

You can use recursion via CTE. You'll want to verify the version of SQLite you're using supports CTEs.

WITH Dataset AS (
SELECT ID
,Name
,0 AS Position
FROM Tbl
LIMIT 1
)
,SPLIT AS (
SELECT ID
,substr(Name, 1, instr(Name,'_') - 1) AS Value
,substr(Name, instr(Name,'_') + LENGTH('_')) AS Remainder
,instr(Name,'_') AS Position
,1 AS Ordinal
FROM Dataset
UNION ALL
SELECT ID
,substr(Remainder, 1, instr(Remainder,'_') - 1) AS Value
,substr(Remainder, instr(Remainder,'_') + LENGTH('_')) AS Remainder
,instr(Remainder,'_') AS Position
,Ordinal + 1 AS Ordinal
FROM SPLIT
WHERE LENGTH(Remainder) > 15
)
SELECT *
FROM SPLIT
ORDER BY ID, Ordinal;

Thi B

3/4/2019 20:34:51

Hi Nikkos,
I have a table name 'tritonia' and column name 'prot_id' with string values Fi_2.p2. I want to separate the string into 2 columns between the part before and after the dot.
When i used your method to split the string like this:
SELECT substr(prot_id, 1, pos-1) AS geneid,
substr(prot_id, pos+1) AS prot
FROM (SELECT *,instr(prot_id,'.') AS pos FROM tritonia)
it works. but when I tried to update the 'tritonia' table with 2 new columns name geneid and prot like this:
UPDATE tritonia
SET geneid = (SELECT substr(prot_id, 1, pos-1)
WHERE geneid=tritonia.prot_id),
prot = (SELECT substr(prot_id, pos+1)
WHERE prot=tritonia.prot_id)
FROM (SELECT *,instr(prot_id,'.') AS pos FROM tritonia;

then i got an error: near "FROM": syntax error.

I wonder if you can tell me where did i get wrong?
Thanks

Wm Fiske

21/10/2021 10:13:56

Thank you!