-- 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)