SQLite Forum

[Feature Request] split string table-valued function
> 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:
grid (parent, head, tail, idx) as
    select parent, '', files||' ', 0
      from grids_ssv
    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