> If a "split()" function did exist
To *wrap-up* this thread, and hopefully convince Richard and team to add a `split`
table-valued function to SQLite Core, I'd like the illustrate the stark difference
in SQL necessary w/ and w/o such a function.
So I created two concrete tables, with the same data, one with the original
space-separated values, and the other using JSON. And to build the JSON-formatted
one, I actually used the `grid` view using a CTE that Ryan helped me define:
```
create table grids_ssv as
select parent, files from grids
create table grids_json as
select parent, '["'||group_concat(file, '","')||'"]' as files
from (select parent, file, idx from grid order by parent, idx)
group by parent
```
Here the **simple** SQL using JSON1:
```
select g.parent, j.id, j.value
from grids_json g, json_each(files) j
order by g.parent, j.id
```
And here's the **complex** CTE from Ryan and myself:
```
with
grid (parent, head, tail, idx) as
(
select parent, '', files||' ', 0
from grids_ssv
UNION ALL
select parent,
trim(substr(tail, 1, instr(tail, ' ')-1)) as head,
substr( tail, instr( tail, ' ' ) + 1) as tail,
idx + 1 as idx
from grid
where instr(tail, ' ') > 0
)
select distinct parent, head as file, idx
from grid
where length(trim(head)) > 0
order by parent, idx
```
There's no contest IMHO. If this doesn't convince anything this belongs in SQLite's built-in functions,
I don't know what will basically.
In my case, I don't have enough data to contrast performance. 21 rows expand into 82 rows.
Both queries range from 1ms to 4ms in SQLiteSpy, depending on the run. But we mostly all
agree the table-valued one will fair better for larger data, especially larger strings to split.
On the `join` side, that's a *keyword*, and `group_concat` already does that basically,
so I don't know what I was thinking when I wrote my original feature request ;)
So Richard, could we please have something like `split`? Xmas is not far :) --DD