Split space separated values and map them to original ID in SQLite?

Question

I have a table called personal_websessions that contains data in the following format:

 id_no | website_link 
 1     | google.com msn.com gmail.com 
 2     | stackoverflow.com reddit.com 
 3     | msn.com

You can create this table using the following SQL commands:

CREATE TABLE personal_websessions(id_no INTEGER PRIMARY KEY, website_link TEXT);
INSERT INTO personal_websessions VALUES(1, 'google.com msn.com gmail.com'), (2, 'stackoverflow.com reddit.com'), (3, 'msn.com ');

I would like to split the values of the website_link column by the spaces ' ' to attain the following table result:

id_no | website_link 
1     | google.com
1     | msn.com 
1     | gmail.com
2     | stackoverflow.com
2     | reddit.com
3     | msn.com

I would like to split the website_link column by a single space to achieve this - I have tried different methods including those outlined here:

But this example did not help that much as it was for comma separated not space separated

I know there is a way to do this using sqlite but I just haven't figured it out yet! Any help is greatly appreciated!

Thanks - Goosfraba

Why not simply fix your data model to store each website link in a different row? — GMB, Jan 17 '20 at 09:41
You should fix your source data before you bring it into SQLite. — Tim Biegeleisen, Jan 17 '20 at 09:43
I do not have access to the data model at hand - i only have this table, which is why i am looking to split it by space separation. — Beans On Toast, Jan 17 '20 at 09:44
I do not have access to the data source before SQLite - could you suggest any help using sqlite regarding my question? — Beans On Toast, Jan 17 '20 at 09:44

score 1 · Answer 1 · answered Jan 17 '20 at 11:20

1

With a recursive CTE:

with recursive cte as (
  select id_no, trim(website_link) || ' ' website_link,
    substr(
      website_link, 
      1, 
      case 
        when website_link like '% %' then instr(website_link, ' ') - 1
        else website_link
      end
    ) link
  from personal_websessions
  union all                                                     
  select c.id_no, substr(c.website_link, length(c.link) + 2),
    substr(
      substr(c.website_link, length(c.link) + 2), 
      1, 
      instr(substr(c.website_link, length(c.link) + 2), ' ') - 1
    ) link
  from cte c                                               
  where substr(c.website_link, length(c.link) + 2) like '% %'
)
select id_no, link website_link
from cte
order by id_no

See the demo.
Results:

| id_no | website_link      |
| ----- | ----------------- |
| 1     | google.com        |
| 1     | msn.com           |
| 1     | gmail.com         |
| 2     | stackoverflow.com |
| 2     | reddit.com        |
| 3     | msn.com           |

answered Jan 17 '20 at 11:20

forpas

160,666
10
38
76

Thanks for this - could you clarigy what the +2 part is doing here in this function? is substr(c.website_link, length(c.link) + 2) – Beans On Toast Jan 17 '20 at 11:29
1

In the string `google.com msn.com gmail.com`, the 1st time the cte returns `link = google.com`, so next time must omit the first 10 chars (because the length of `google.com` is 9 + 1 space after that) and start from the 11th position = 9 + 2 – forpas Jan 17 '20 at 11:35
Ah thats quite clever! Will have a look at this some more then will aceept answer! thanks buddy! – Beans On Toast Jan 17 '20 at 11:40
I have tried this but i am finding that there may be some errors in the cte - due to it being recursive it seems to be picking up more spaces - the answer below which i have accepted is working more efficiently – Beans On Toast Jan 17 '20 at 13:22
Did you check the demo? The code works if your actual data is like the sample data you posted. If it is not then you should have mentioned it. – forpas Jan 17 '20 at 15:01

score 1 · Accepted Answer · answered Jan 17 '20 at 12:00

A recursive CTE is the best approach in SQLite. I prefer a version like this:

with cte(id_no, website_link, rest, lev) as (
      select pw.id_no, NULL as website_link, trim(pw.website_link) || ' ' as rest, 1 as lev
      from personal_websessions pw
      union all
      select cte.id_no,
             trim(substr(cte.rest, 1, instr(cte.rest, ' '))),
             substr(cte.rest, instr(cte.rest, ' ') + 1),
             lev + 1
      from cte
      where rest <> '' 
     )
select id_no, website_link
from cte
where website_link <> ''
order by id_no;

Here is a db<>fiddle.

In this particular version, all the string manipulation is in the recursive portion of the CTE. The anchor just sets up the data.

The use of trim makes the query more robust if the spaces are irregular -- for instance, you have a space at the end of one of the text values.

As for lev, I almost always include that in recursive CTEs so I can see the depth of recursion, if I want.

All that said, this is not a good way to store data. You should be using a separate table whose structure is more like the result set you are getting -- one row per id and unique website.

Thank you for your help - I appreciate that I should be storing the data in this separated format as it would make more sense from a RDMS perspective and would do so in future, but for the time being, your answer has allowed me to see the use of CTE and recursion to solve a problem - thank you and answer accepted — Beans On Toast, Jan 17 '20 at 13:23

Split space separated values and map them to original ID in SQLite?

2 Answers2