Split slash in Oracle SQL

Question

Hi I need split text in Oracle SQL,

Input and output shown in picture. In first secture I have orginal data in one column ( in picture A column). In second secture which I need to return back(output).

my query is :

select val,
      CASE 
            when substr(val, 1, instr(val, '/') - 1)   is null then   val 
            ELSE substr(val, 1, instr(val, '/') - 1) 
      end as "LEVEL1",
      substr(VAL, 
             instr(val, '/',1)+1, 
             instr(val, '/',1,2)-instr(val, '/',1)-1) "LEVEL2",
      substr(VAL, instr(val, '/') ) "aparat3"
from rmtd1.split_row;

Answer 1

You don't really need more than regexp_substr()

select 
  val, 
  regexp_substr (val,'(^|/)([^/]*)',1, 1, null, 2) as level1,
  regexp_substr (val,'(^|/)([^/]*)',1, 2, null, 2) as level2,
  regexp_substr (val,'(^|/)([^/]*)',1, 3, null, 2) as level3,
  regexp_substr (val,'(^|/)([^/]*)',1, 4, null, 2) as level4
from your_table;

1. (^|/) finds the beginning or a /
2. ([^/]*) catches everything that comes after each hit from (^|/)
3. 3rd parameter (1) tells it where to start in your input
4. 4th parameter (1-4) selects which of your hits is returned
5. 5th parameter lets you tweak how matches are determined
6. last parameter selects the hit from your second subexpression ([^/]*) in point 2. above

demo