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:

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 :)

CodePudding user response：

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

CodePudding user response：

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.