Nikos Papandreou
  • Home
  • Profile
  • Programming
    • Anonymous Outlook
    • Autorun Generator
    • Compiler
  • Blog

SQLite split string

15/10/2013

14 Comments

 
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!

Reply
yodann link
6/9/2015 08:09:13

thank u. it's amazing!

Reply
Alice
4/4/2016 09:29:25

I love you!

Reply
scientist
29/7/2016 10:31:17

Great! God bless you

Reply
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


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

Reply
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?

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

Reply
Tomek link
13/11/2017 15:53:36

Thank you! ;)

Reply
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

Reply
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;

Reply
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

Reply
Wm Fiske
21/10/2021 10:13:56

Thank you!

Reply



Leave a Reply.

    Categories

    All
    Bitbucket
    Forefront
    Mercurial
    Php
    Sqlite
    Ssh
    Windows

    Archives

    October 2015
    November 2014
    March 2014
    October 2013

    RSS Feed