CSV in text field is one of the best alternative to the missing array type. There is group_concat aggregate but no split counterpart. It would be nice to implement one to unnest values into rows.

INSERT INTO votes(topic,user,comment)
VALUES ('Add split function','me','yes please!');

-- urgh.
-- Lots of ways this will fail.
WITH RECURSIVE 
  init(text,sep) AS (
    VALUES('here are some words and stuff',' ')),
  split(i,t,a) AS (
    SELECT 0,TRIM(text,sep)||sep,JSON_ARRAY() FROM init
	  UNION ALL 
    SELECT
        i+1,
	SUBSTR(t,INSTR(t,sep)+1),
	JSON_INSERT(a,'$[#]',SUBSTR(t,1,INSTR(t,sep)-1))
    FROM split JOIN init
    WHERE INSTR(t,sep)>0
    LIMIT 100)
SELECT a FROM split WHERE i=(SELECT MAX(i) FROM split)

a
["here","are","some","words","and","stuff"]

EDIT: there are better implementations already on the original post. EDIT2: The OP here wanted ROWS not an array.

I wonder if some the existing FTS5 tokenizer functionality could be leveraged to do the splitting? (i.e. perhaps expose some of its workings as a function, dunno)

Be aware that sqlite3 does have an array type - comes with JSON libraries.

So if you were to convert your 'csv' text into a JSON_ARRAY, you can then manipulate with the JSON functions - in this case probably the json_extract function.

In fact; you are no longer limited to arrays either - you can have object, nested structures etc - whatever JSON can do in fact.

It would still be nice to have a 'split' function though I think.

sqlite3 does have an array type

That's an exageration :). There are only 5 types: null, integer, real, text, and blob.

The same way SQLite does NOT have date or time or datetime or timestamp types, but functions to manipulate the text or numeric values as such.

By the same token, SQLite's JSON1 extension allows to manipulate text values as JSON, including arrays and objects. But that doesn't make it a true array type.

The closest SQLite has to custom types, beyond the 5 basic ones, are subtypes and pointer types. And those are purely transient, preserved only between nested function invocations mainly, AFAIK.

Storage-wise, it's the 5 basic types, and nothing else. --DD

Yup - I got that wrong - there is no 'type' of array etc. I just meant that there are ways of handling arrays (json arrays) I guess. (As opposed to dealing with pure strings)

Point taken!

As I wrote here, in the meantime you could convert your string into a JSON array like

'['||replace(json_quote(your_string),',','","')||']'

and use e.g. json_each to split to rows

sqlite> select value
   ...> from json_each('['||replace(json_quote('hello,world'),',','","')||']')
   ...> order by key;

hello
world

from json_each('['||replace(json_quote('hello,world'),',','","')||']')

Yeah, constrast that with from split('hello,world') :)

That's exactly why we need a split table-valued function!

Yours is a great tip, don't get me wrong. We just need something better and simpler.

Indeed - that's why I wrote in the meantime :-)

And I prefer this over the recursive CTE with a bunch of instr & substr and all the bookkeeping of indexes. Here, it's just an (ugly) chain of function calls and string concatenation.

Given the complete set of "joiners" and "splitters" (json_array & json_extract for columns, json_group_array & json_each for rows) in the JSON extension, I tend do store any array as JSON for the time being.

This will work (using the previously mentioned statement_vtab) (see this post)

drop table if exists split;
create virtual table split using statement((
with tokens(entry, data, sep, valid) as
     (
        select null,
               :data,
               :sep,
               0
      union all
         select substring(data, 1, instr(data, sep) - 1),
                substring(data, instr(data, sep) + 1),
                sep,
                1
           from tokens
          where instr(data, sep) > 0
      union all
         select data,
                null,
                sep,
                1
           from tokens
          where instr(data, sep) < 1
     )
select entry
  from tokens
 where valid == 1
));

which will let you do, for example:

sqlite> select entry from split('this is a list of space separated tokens', ' ');
┌─────────────┐
│    entry    │
├─────────────┤
│ 'this'      │
│ 'is'        │
│ 'a'         │
│ 'list'      │
│ 'of'        │
│ 'space'     │
│ 'separated' │
│ 'tokens'    │
└─────────────┘

Not exactly builtin, but seamless nonetheless.

Note that if you combine this with my ToBestType function, you can do like the following:

drop table if exists split;
create virtual table if not exists split using statement((
with tokens(entry, data, sep, valid) as
     (
        select null,
               :data,
               :sep,
               0
      union all
         select substring(data, 1, instr(data, sep) - 1),
                substring(data, instr(data, sep) + 1),
                sep,
                1
           from tokens
          where instr(data, sep) > 0
      union all
         select data,
                null,
                sep,
                1
           from tokens
          where instr(data, sep) < 1
     )
select ToBestType(entry) as entry
  from tokens
 where valid == 1
));

so that you can get, for example:

sqlite> select entry, typeof(entry) from split('this,is,a,list,15,of comma,,separated,7.2,tokens', ',');
┌─────────────┬───────────────┐
│    entry    │ typeof(entry) │
├─────────────┼───────────────┤
│ 'this'      │ 'text'        │
│ 'is'        │ 'text'        │
│ 'a'         │ 'text'        │
│ 'list'      │ 'text'        │
│ 15          │ 'integer'     │
│ 'of comma'  │ 'text'        │
│ NULL        │ 'null'        │
│ 'separated' │ 'text'        │
│ 7.2         │ 'real'        │
│ 'tokens'    │ 'text'        │
└─────────────┴───────────────┘

This version seems to generate the best execution plan in that it avoids repeated redundant calls to the same function ... rather it uses separate branches in the recursion to perform the calculations one time only. This uses more ephemeral table space (about twice as much), but less CPU (about half as much).

drop table if exists split;
create virtual table if not exists split using statement((
   with recursive
        input(data, sep) as
        (
           values (:data, :sep)
        ),
        tokens(token, data, sep, pos, isValid) as
        (
           select null,
                  data,
                  sep,
                  instr(data, sep),
                  false
             from input
         union all
            select substr(data, 1, pos - 1),
                   substr(data, pos + 1),
                   sep,
                   -1,
                   true
              from tokens
             where pos > 0
         union all
            select null,
                   data,
                   sep,
                   instr(data, sep),
                   false
              from tokens
             where pos < 0
         union all
            select data,
                   null,
                   sep,
                   null,
                   true
              from tokens
            where pos == 0
       )
select ToBestType(token) as value
  from tokens
 where isValid
));

Note that if you change values (:data, :sep) to values (:data, coalesce(:sep, ',')) the split is the opposite of group_concat (in that the separator, if unspecified, is ','):

select value 
  from split((
              select group_concat(value)
                from generate_series
               where start=1 
                 and stop=10
            ));
┌───────┐
│ value │
├───────┤
│ 1     │
│ 2     │
│ 3     │
│ 4     │
│ 5     │
│ 6     │
│ 7     │
│ 8     │
│ 9     │
│ 10    │
└───────┘

Then there is this way to do it, which does not use recursion. Instead is scans the data for separators and then extracts a row for each one.

with inputs(data, lendata, sep, lensep) as
     (
         select data || sep,
                length(data || sep),
                sep,
                length(sep)
           from (
                    select :data as data,
                           coalesce(:sep, ',') as sep
                )
     ),
     separators(location) as
     (
         select 1 - lensep
           from inputs
      union all
         select value
           from generate_series
           join inputs
          where start = 1
            and stop = lendata
            and substring(data, value, lensep) == sep
     ),
     ranges(start, length) as
     (
         select location + lensep,
                lead(location) over () - location - lensep
           from separators, inputs
     )
select ToBestType(substring(data, start, length)) as value
  from ranges, inputs
 where length is not null

In case anyone is looking for a 3rd party solution to this: sqlite-lines can mostly do this, is MIT licensed, and you can download pre-compiled loadable binaries (or build yourself).

https://github.com/asg017/sqlite-lines

.load ./lines0

-- lines_read will read each line from a file
select rowid, line
from lines_read('file.txt');

-- lines() works in in-memory strings and blobs
select rowid, line
from lines('a
b
c');
/*
1,a
2,b
3,c
*/

-- use a different delimiter, 1-character only
select rowid, line
from lines('a|b|c', '|');
/*
1,a
2,b
3,c
*/

No windows support, since getdelim() isn't available. Happy to take in contributions, however!