hi all,
i want to extract 2nd value which is vary in length like:
1/2/
1/34/7
1/555/8 and so on
my below code is working fine, checked on oracle livesql website, but it seems that here I used parent_path,3 is not dynamic, how i can add here logic which not restrict me to search from 3rd character this way, instead it should search for 2nd occurance of slash / and minus 1 to get value in between first 2 slashes?
my code:
with dt as (
select '1/' as parent_path from dual union all
select '1/2/' as parent_path from dual union all
select '1/12/13' as parent_path from dual union all
select '1/4/20' as parent_path from dual union all
select '1/666/40' as parent_path from dual
)
select substr(substr(parent_path,3),1,instr(substr(parent_path,3),'/')-1) abc from dt
above result shows 2, 12, 4, 666 ( here i mentioned it comma separated because can't copy --i don't know how to for result set-- from oracle live sql )
and should ignore for first entry which returns nothing.
regards