Oracle截取JSON字符串内容的方法-编写函数实现

Oracle截取JSON字符串内容 ，具体代码如下所示：

CREATE OR REPLACE FUNCTION get\_valus(p\_jsonstr varchar2,startkey varchar2) RETURN VARCHAR2  
IS  
rtnVal VARCHAR2(1000);  
FindIdxS NUMBER(2);  
FindIdxE NUMBER(2);  
BEGIN  
if substr(p\_jsonstr,(instr(p\_jsonstr,startkey)+length(startkey) +2),(instr(p\_jsonstr,',',instr(p\_jsonstr,startkey))-instr(p\_jsonstr,startkey)-length(startkey)-2)) is not null  
then  
rtnVal:=substr(p\_jsonstr,(instr(p\_jsonstr,startkey)+length(startkey) +2)  
,(instr(p\_jsonstr,',',instr(p\_jsonstr,startkey))-instr(p\_jsonstr,startkey)-length(startkey)-2));  
else  
rtnVal:=substr(p\_jsonstr,(instr(p\_jsonstr,startkey)+length(startkey) +2)  
,(instr(p\_jsonstr,'}',instr(p\_jsonstr,startkey))-instr(p\_jsonstr,startkey)-length(startkey)-2));  
end if;  
RETURN rtnVal;  
END get\_valus;  

参数说明：

p_jsonstr：目标JSON字符串
startkey：需要截取的JSON内容key值

例句

SELECT t.id,t.ext1,get_valus(t.ext1,‘FullAccountName’) FROM table_name t