Split a string into rows using pure SQLite

This is possible with a recursive common table expression:

WITH RECURSIVE split(s, last, rest) AS (  VALUES('', '', 'C:\Users\fidel\Desktop\Temp')  UNION ALL  SELECT s || substr(rest, 1, 1),         substr(rest, 1, 1),         substr(rest, 2)  FROM split  WHERE rest <> '')SELECT sFROM splitWHERE rest = ''   OR last = '\';

^{(You did not ask for a reasonable way.)}

Recursive CTE:

WITH RECURSIVE cte(org, part, rest, pos) AS (  VALUES('C:\Users\fidel\Desktop\Temp', '','C:\Users\fidel\Desktop\Temp'|| '\', 0)  UNION ALL  SELECT org,         SUBSTR(org,1, pos + INSTR(rest, '\')),         SUBSTR(rest, INSTR(rest, '\')+1),         pos + INSTR(rest, '\')  FROM cte  WHERE INSTR(rest, '\') > 0                         )SELECT *FROM cteWHERE pos <> 0ORDER BY pos; 

SqlFiddleDemo

Output:

╔═════════════════════════════╗║            part             ║╠═════════════════════════════╣║ C:\                         ║║ C:\Users\                   ║║ C:\Users\fidel\             ║║ C:\Users\fidel\Desktop\     ║║ C:\Users\fidel\Desktop\Temp ║╚═════════════════════════════╝

How it works:

org  - original string does not changepart - simply `LEFT` equivalent of original string taking pos number of charsrest - simply `RIGHT` equivalent, rest of org stringpos  - position of first `\` in the rest 

Trace:

╔══════════════════════════════╦══════════════════════════════╦════════════════════════════╦═════╗║             org              ║            part              ║           rest             ║ pos ║╠══════════════════════════════╬══════════════════════════════╬════════════════════════════╬═════╣║ C:\Users\fidel\Desktop\Temp  ║ C:\                          ║ Users\fidel\Desktop\Temp\  ║   3 ║║ C:\Users\fidel\Desktop\Temp  ║ C:\Users\                    ║ fidel\Desktop\Temp\        ║   9 ║║ C:\Users\fidel\Desktop\Temp  ║ C:\Users\fidel\              ║ Desktop\Temp\              ║  15 ║║ C:\Users\fidel\Desktop\Temp  ║ C:\Users\fidel\Desktop\      ║ Temp\                      ║  23 ║║ C:\Users\fidel\Desktop\Temp  ║ C:\Users\fidel\Desktop\Temp  ║                            ║  28 ║╚══════════════════════════════╩══════════════════════════════╩════════════════════════════╩═════╝

If you want to search for the values ​​individually, use the code below:

WITH RECURSIVE split(content, last, rest) AS (VALUES('', '', 'value1§value2§value3§value4§value5§value6§value7')UNION ALL  SELECT     CASE WHEN last = '§'             THEN                substr(rest, 1, 1)            ELSE                content || substr(rest, 1, 1)    END,     substr(rest, 1, 1),     substr(rest, 2)  FROM split  WHERE rest <> '')SELECT        REPLACE(content, '§','') AS 'ValueSplit'     FROM        splitWHERE        last = '§' OR rest ='';

Result:

**ValueSplit**value1value2value3value4value5value6value7

I hope I can help people with the same problem.