1

Topic: Recursion from text value

Good afternoon!
There was a problem solved on MS. Now I do not know as this artful CTE to shift on CONNECT BY PRIOR
a recursion from text value
If laziness to open, its description:
There is a hierarchy:
Name, the code
data1,01
data2,01.350
data3,01.350.10
data4,01.350.10.10
data5,01.350.10.10.001
data6,01.350.10.10.001.01
It is obvious that "fatherly" subdividing is divided by restriction of points.
Question - how to construct type structure:
Name, the code
data1, data1
data2, data1 / data2
data3, data1 / data2 / data3
data4, data1 / data2 / data3 / data4
data5, data1 / data2 / data3 / data4 / data5
data6, data1 / data2 / data3 / data4 / data5 / data6

2

Re: Recursion from text value

with mytab (n, s) as (
select 1, ' 01 ' from dual union all
select 2, ' 350 ' from dual union all
select 3, ' 10 ' from dual union all
select 4, ' 12 ' from dual union all
select 5, ' 001 ' from dual union all
select 6, ' 01 ' from dual
)
select substr (SYS_CONNECT_BY_PATH (s, '. '), 2) as P from mytab
start with n=1
connect by n = level-1
;
P
--------------------------------------------------------------------------------
01
01.01
01.01.350
01.01.350.10
01.01.350.10.12
01.01.350.10.12.001
01.01.350.10.12.001.01

3

Re: Recursion from text value

fedoamx;

with mytab (n, s) as (
select 1, ' 01 ' from dual union all
select 2, ' 350 ' from dual union all
select 3, ' 10 ' from dual union all
select 4, ' 12 ' from dual union all
select 5, ' 001 ' from dual union all
select 6, ' 01 ' from dual
)
select substr (SYS_CONNECT_BY_PATH (s, '. '), 2) as P from mytab
start with n=1
connect by n = level
;
P
--------------------------------------------------------------------------------
01
01.350
01.350.10
01.350.10.12
01.350.10.12.001
01.350.10.12.001.01

The minus forgot to delete smile

4

Re: Recursion from text value

fedoamx;

SQL> with t (n, c) as
2 (select ' data1 ', ' 01 ' from dual
3 union all select ' data2 ', ' 01.350 ' from dual
4 union all select ' data3 ', ' 01.350.10 ' from dual
5 union all select ' data4 ', ' 01.350.10.10 ' from dual
6 union all select ' data5 ', ' 01.350.10.10.001 ' from dual
7 union all select ' data6 ', ' 01.350.10.10.001.01 ' from dual)
8 select level l, t. *, substr (sys_connect_by_path (n, ' / '), 4) path
9 from t
10 start with instr (c, '. ') = 0
11 connect by regexp_like (c, ' ^ ' || prior c || '\.\d + $ ');
L N C PATH
---------- ----- ------------------- --------------------------------------------------
1 data1 01 data1
2 data2 01.350 data1 / data2
3 data3 01.350.10 data1 / data2 / data3
4 data4 01.350.10.10 data1 / data2 / data3 / data4
5 data5 01.350.10.10.001 data1 / data2 / data3 / data4 / data5
6 data6 01.350.10.10.001.01 data1 / data2 / data3 / data4 / data5 / data6
6 rows selected.

It is possible  normal like instead of regexp_like then the index on c can be picked up if it is, but the task all the same the strange.

5

Re: Recursion from text value

with t as (
select ' data1 ' n, ' 01 ' c from dual union all
select ' data2 ', ' 01.350 ' from dual union all
select ' data3 ', ' 01.350.10 ' from dual union all
select ' data4 ', ' 01.350.10.10 ' from dual union all
select ' data5 ', ' 01.350.10.10.001 ' from dual union all
select ' data6 ', ' 01.350.10.10.001.01 ' from dual
);
cte (
n;
nFull;
c
) as (
select n;
n nFull;
c
from t
where c not like ' %. %'
union all
select t.n;
nFull || ' / ' || t.n;
t.c
from cte c;
t
where t.c like c.c || '. %'
and substr (t.c, length (c.c) + 2) not like ' %. %'
)
select *
from cte
/
N NFULL A C
----- ---------------------------------------- -------------------
data1 data1 01
data2 data1/data2 01.350
data3 data1/data2/data3 01.350.10
data4 data1/data2/data3/data4 01.350.10.10
data5 data1/data2/data3/data4/data5 01.350.10.10.001
data6 data1/data2/data3/data4/data5/data6 01.350.10.10.001.01
6 rows selected.
SQL>

SY.

6

Re: Recursion from text value

SY;
11

ERROR:
ORA-01489: result of string concatenation is too long

cast (' data1 ' as varchar2 (xxxx))
.....
stax

7

Re: Recursion from text value

dbms_photoshop;
Thanks!

8

Re: Recursion from text value

All thanks, it was possible only while the method dbms_photoshop to try.