WITH t AS ( SELECT 'A/w/b' v FROM dual ) SELECT v , LENGTH(v) - LENGTH(REPLACE(v, '/')) + 1 cnt , lv , SUBSTR( v , INSTR('/'||v, '/', 1, lv) , INSTR(v||'/', '/', 1, lv) - INSTR('/'||v, '/', 1, lv) ) x FROM t , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 9) WHERE lv <= LENGTH(v) - LENGTH(REPLACE(v, '/')) + 1 ;