SQL - INSTR & SUBSTR example

-- -----------------------------
--
-- Reverse search using "/" as a separator.
--
-- -----------------------------

col List format a35

select
substr('This/is/a/separation/list',INSTR('This/is/a/separation/list', '/', -1, 5)+1,length('This/is/a/separation/list'))||chr(10)||
substr('This/is/a/separation/list',INSTR('This/is/a/separation/list', '/', -1, 4)+1,length('This/is/a/separation/list'))||chr(10)||
substr('This/is/a/separation/list',INSTR('This/is/a/separation/list', '/', -1, 3)+1,length('This/is/a/separation/list'))||chr(10)||
substr('This/is/a/separation/list',INSTR('This/is/a/separation/list', '/', -1, 2)+1,length('This/is/a/separation/list'))||chr(10)||
substr('This/is/a/separation/list',INSTR('This/is/a/separation/list', '/', -1, 1)+1,length('This/is/a/separation/list')) List
 from dual
/

-- -----------------------------
--
-- Find how many occurrences of "/" appear
--
-- -----------------------------

variable main_str varchar2(30)
variable srch_str varchar2(20)
exec :main_str := 'This/is/a/separation/list';
exec :srch_str := '/';

select (length(:main_str) - nvl(length(replace(:main_str,:srch_str,'')),0))/
          length(:srch_str) cnt
from dual;

select
substr(:main_str,INSTR(:main_str, :srch_str, -1, 5)+1,length(:main_str))||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 4)+1,length(:main_str))||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 3)+1,length(:main_str))||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 2)+1,length(:main_str))||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 1)+1,length(:main_str)) List
 from dual
/

-- -----------------------------
--
-- Find how many occurrences of "/" appear
--
-- -----------------------------
col cnt heading "How many occurrances of /"

select
substr(:main_str,INSTR(:main_str, :srch_str, -1, 5)+1,length(:main_str))||chr(10)||
(length(substr(:main_str,INSTR(:main_str, :srch_str, -1, 5)+1,length(:main_str)))- nvl(length(replace(substr(:main_str,INSTR(:main_str, :srch_str, -1, 5)+1,length(:main_str)),:srch_str,'')),0))/length(:srch_str)||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 4)+1,length(:main_str))||chr(10)||
(length(substr(:main_str,INSTR(:main_str, :srch_str, -1, 4)+1,length(:main_str)))- nvl(length(replace(substr(:main_str,INSTR(:main_str, :srch_str, -1, 4)+1,length(:main_str)),:srch_str,'')),0))/length(:srch_str)||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 3)+1,length(:main_str))||chr(10)||
(length(substr(:main_str,INSTR(:main_str, :srch_str, -1, 3)+1,length(:main_str)))- nvl(length(replace(substr(:main_str,INSTR(:main_str, :srch_str, -1, 3)+1,length(:main_str)),:srch_str,'')),0))/length(:srch_str)||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 2)+1,length(:main_str))||chr(10)||
(length(substr(:main_str,INSTR(:main_str, :srch_str, -1, 2)+1,length(:main_str)))- nvl(length(replace(substr(:main_str,INSTR(:main_str, :srch_str, -1, 2)+1,length(:main_str)),:srch_str,'')),0))/length(:srch_str)||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 1)+1,length(:main_str))||chr(10)||
(length(substr(:main_str,INSTR(:main_str, :srch_str, -1, 1)+1,length(:main_str)))- nvl(length(replace(substr(:main_str,INSTR(:main_str, :srch_str, -1, 1)+1,length(:main_str)),:srch_str,'')),0))/length(:srch_str) cnt
 from dual
/