ORACLE_截取字符串中指定的字符和值

最新推荐文章于 2024-01-31 09:05:26 发布

jay_lishijie

最新推荐文章于 2024-01-31 09:05:26 发布

阅读量635

点赞数

文章标签： oracle newline equals function null

本文链接：https://blog.csdn.net/jay_lishijie/article/details/4517984

版权

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;
/