-- String parsing using both SUBSTR And INSTR 
 
 
- get ALL characters up TO the FIRST comma
SELECT SUBSTR('abc,def,ghi', 1 ,INSTR('abc,def,ghi', ',', 1, 1)-1)
FROM dual;
 
 
-- get the character(s) in between the commas
SELECT SUBSTR('abc,def,ghi', INSTR('abc,def,ghi',',', 1, 1)+1,
INSTR('abc,def,ghi',',',1,2)-INSTR('abc,def,ghi',',',1,1)-1)
FROM dual;
 
 
-- get the character(s) after the last comma
SELECT SUBSTR('abc,def,ghi', INSTR('abc,def,ghi',',', 1, 2)+1)
FROM dual; 
 
 
-- find the first blank from the right 
-- the -1 parameter of INSTR indicates that we're searching 
-- for the first occurrence going backwards from the end of 
-- the column.
 
     SELECT SUBSTR(name
                 ,INSTR(name,' ',-1)+1
                 ) AS surname
     FROM test_table
 
 
 
-- a PL/SQL function that takes two strings representing a 
-- list of numbers separated by commas and returns a string 
-- representing the list of each nth element added together.
 
CREATE OR REPLACE FUNCTION test_func(p_arg1 VARCHAR2, p_arg2 VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
 IF ( INSTR(p_arg1,',') = 0 AND INSTR(p_arg2,',') = 0 ) THEN
  RETURN TO_NUMBER(p_arg1) + TO_NUMBER(p_arg2);
 ELSIF (INSTR(p_arg1,',') = 0 OR INSTR(p_arg2,',') = 0) THEN
  raise_application_error(-20001, 'Length of the strings are not equal');
 ELSE
  RETURN TO_CHAR(TO_NUMBER(SUBSTR(p_arg1, 1, INSTR(p_arg1,',') - 1)) + TO_NUMBER(SUBSTR(p_arg2, 1, INSTR(p_arg2,',') - 1)))
         ||','||
         test_func(SUBSTR(p_arg1, INSTR(p_arg1,',') + 1 ), SUBSTR(p_arg2, INSTR(p_arg2,',') + 1 ));
 END IF;
END;
/
 
 
 
-- the Linux command "basename" is most famous for taking a 
-- full file path string and stripping away the leading path 
-- component, returning just the name of the file. This can 
-- be emulated in PL/SQL with calls to SUBSTR and INSTR, 
-- like this:
 
SUBSTR(dirname,INSTR(dirname,'/',-1)+1)