'Recursive split of path with H2 DB and SQL

I've path names of the following common form (path depth not limited):

/a/b/c/d/e/...

Example

/a/b/c/d/e

Expected result

What I'd like to achieve now is to split the path into a table containing the folder and the respective parent:

parent folder
/a/b/c/d/ e
/a/b/c/ d
/a/b/ c
/a/ b
/ a

The capabilities of the H2 db are a bit limited when it comes to splitting strings, thus my assumption was it must be solved recursively (especially since the path depth is not limited).

Any help would be appreciated :)

sqlh2


Solution 1:[1]

Do something like this:

with recursive 
  p(p) as (select '/a/b/c/d/e' as p),
  t(path, parent, folder, i) as (
    select 
      p, 
      REGEXP_REPLACE(p, '(.*)/\w+', '$1'),
      REGEXP_REPLACE(p, '.*/(\w+)', '$1'), 
      1 
    from p
    union
    select 
      t.parent, 
      REGEXP_REPLACE(t.parent, '(.*)/\w+', '$1'),
      REGEXP_REPLACE(t.parent, '.*/(\w+)', '$1'), 
      t.i + 1
    from t
    where t.parent != ''
  )
select *
from t;

resulting in

|PATH      |PARENT  |FOLDER|I  |
|----------|--------|------|---|
|/a/b/c/d/e|/a/b/c/d|e     |1  |
|/a/b/c/d  |/a/b/c  |d     |2  |
|/a/b/c    |/a/b    |c     |3  |
|/a/b      |/a      |b     |4  |
|/a        |        |a     |5  |

Not sure if you're really interested in trailing / characters, but you can easily fix the query according to your needs.

Solution 2:[2]

You need to use a recursive query, for example:

WITH RECURSIVE CTE(S, F, T) AS (
SELECT '/a/b/c/d/e', 0, 1
UNION ALL
SELECT S, T, LOCATE('/', S, T + 1)
FROM CTE
WHERE T <> 0
)
SELECT
    SUBSTRING(S FROM 1 FOR F) PARENT,
    SUBSTRING(S FROM F + 1 FOR
        CASE T WHEN 0 THEN CHARACTER_LENGTH(S) ELSE T - F - 1 END) FOLDER
FROM CTE WHERE F > 0;

It produces

PARENT FOLDER
/ a
/a/ b
/a/b/ c
/a/b/c/ d
/a/b/c/d/ e

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Lukas Eder
Solution 2 Evgenij Ryazanov

Related Questions