18-43. | Here’s a suggested function:
/* Filename on web page: betwnstr.sql */
CREATE OR REPLACE FUNCTION betwnstr (
str IN VARCHAR2,
start_str IN VARCHAR2,
end_str IN VARCHAR2 := NULL )
RETURN VARCHAR2 IS
-- This function returns the characters in str that occur between
-- the end of start_str and the beginning of end_str.
--
-- If start_str does not occur in str or it occurs after end_str
-- then NULL will be returned.
BEGIN
/*
|| If start_str does occur in str then perform a substring with the
|| following values :
||
|| Start Point = location of start_str + length of start_str
|| Length = location of end_str - Start Point
||
|| Not that if end_str is NULL the natural behaviour
|| of SUBSTR will return all
|| characters after start_str
*/
IF INSTR(str,start_str,1,1) > 0 THEN
RETURN(SUBSTR(str,INSTR(str,start_str,1,1) + LENGTH(start_str),
INSTR(str,end_str,1,1) -
( INSTR(str,start_str,1,1) + LENGTH(start_str))));
ELSE
RETURN(str);
END IF;
END;
|