Env: Oracle 12c
I have the following dataset within a table called: MY_INFO
ID NAME INFO
--------- --------- -----------
1 Isle 4 L 4
2 Isle 0 L 5/0
3 Isle 2 L 6/0/2
4 Isle 2 L 4/2
5 Isle 0 L 4/0
6 Isle 0 L 5/0
7 Isle 2 L 7/0/2
8 Isle 3 L 8/3
9 Isle 2 L 6/0/2
10 Isle 2 L 4/0/2
11 Isle 10 L 10
12 Isle 0 L 11/0
13 Isle 2 L 11/0/2
Given the above records within this MY_INFO table and assuming I am using record ID: 10, i.e.:
ID NAME INFO
--------- --------- -----------
10 Isle 2 L 4/0/2
Like a hierarchy, I need a means of retrieving all records that following the following pattern, for this record atleast.
In this case though, I need to retrieve just the following three records alone that match, in reverse, i.e.
4
4/0
4/0/2
So using INFO value: L 4/0/2, I now need to branch up to L 4/0 and retrieve that record and then all the way back to top level record L 4
So in the end, the records that I expect to retrieve are:
ID NAME INFO
--------- --------- -----------
1 Isle 4 L 4
5 Isle 0 L 4/0
10 Isle 2 L 4/0/2
Unsure how to tackle this using SQL.
Here is a way to do this...
First i "manually" append a '/' in the block check_string, after which in the block row_gen i generate as many rows as there are the number of '/'. So for a string like L 4/0/2 i wold need 2+1(from check_string) rows to generated.
After that im substringing the main string with a combination of instr function, which will tell me the character position of the 1st , 2nd and 3rd '/'.
And lastly i join with the main table to get the list of records based on the actual values in the table.
create table t(id int, name varchar(20), info varchar(50));
insert into t
select 1 ,'Isle 4 ','L 4' from dual union all
select 2 ,'Isle 0 ','L 5/0' from dual union all
select 3 ,'Isle 2 ','L 6/0/2' from dual union all
select 4 ,'Isle 2 ','L 4/2' from dual union all
select 5 ,'Isle 0 ','L 4/0' from dual union all
select 6 ,'Isle 0 ','L 5/0' from dual union all
select 7 ,'Isle 2 ','L 7/0/2' from dual union all
select 8 ,'Isle 3 ','L 8/3' from dual union all
select 9 ,'Isle 2 ','L 6/0/2' from dual union all
select 10,'Isle 2 ','L 4/0/2' from dual union all
select 11,'Isle 10','L 10' from dual union all
select 12,'Isle 0 ','L 11/0' from dual union all
select 13,'Isle 2 ','L 11/0/2' from dual;
with check_string
as (select 'L 4/0/2'||'/' as str /*Here you would pass the string that you want*/
from dual
)
,row_gen
as (
select level as lvl
,instr(str,'/',1,level) as col1
,substr(str
,1
,instr(str,'/',1,level)-1
) as col2
from dual
join check_string
on 1=1
connect by level<=length(str)-length(replace(str,'/'))
)
select *
from row_gen a
join t b
on a.col2=b.info
Output
+----+---------+---------+
| ID | NAME | INFO |
+----+---------+---------+
| 1 | Isle 4 | L 4 |
| 5 | Isle 0 | L 4/0 |
| 10 | Isle 2 | L 4/0/2 |
+----+---------+---------+
dbfiddle link
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=7d974b5d936c04835e730113d28cd4d6