CREATE OR REPLACE FUNCTION splitTTCreateDescription
(
tableField VARCHAR2, -- field in the tbl_wftt_ttcreate
description VARCHAR2, -- description from tbl_wftt_ttcreate
key VARCHAR2 -- field in the description
)
RETURN VARCHAR2
IS
firstPoint INT; -- index where the key appeared
secondPoint INT; -- index where the newline symbol appeared
outStr VARCHAR2(1000); -- return value
otherStr VARCHAR2(1000); -- substring exclude the 'firstpoint'
BEGIN
-- if the 'tableField' does not empty, return 'tableField'
IF (tableField IS NOT NULL) THEN
RETURN tableField;
END IF;
-- if the key does not exist, return empty
IF (INSTR(description, key || '=') = 0) THEN
RETURN '';
END IF;
-- if the key equals the 'Location', no need to get the 'secondPoint'
IF (key <> 'Location') THEN
firstPoint := INSTR(description, key || '=') + LENGTH(key) + 1;
otherStr := TRIM(substr(description, firstPoint));
secondPoint := INSTR(otherStr, CHR(10));
outStr := TRIM(SUBSTR(otherStr, 0, secondPoint));
ELSE
firstPoint := INSTR(description, key || '=') + LENGTH(key) + 1;
outStr := TRIM(substr(description, firstPoint));
END IF;
-- remove the newline symbol
RETURN REPLACE(outStr, CHR(10), '');
END;
/