sql - Split a string into rows using pure SQLite

Question

Using SQLite, I'd like to split a string in the following way.

Input string:

C:UsersfidelDesktopTemp

and have the query return these rows:

C:
C:Users
C:Usersfidel
C:UsersfidelDesktop
C:UsersfidelDesktopTemp

In other words, I'd like to split a file path into its constituent paths. Is there a way to do this in pure SQLite?

Answer 1

This is possible with a recursive common table expression:

WITH RECURSIVE split(s, last, rest) AS (
  VALUES('', '', 'C:UsersfidelDesktopTemp')
  UNION ALL
  SELECT s || substr(rest, 1, 1),
         substr(rest, 1, 1),
         substr(rest, 2)
  FROM split
  WHERE rest <> ''
)
SELECT s
FROM split
WHERE rest = ''
   OR last = '';

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