Oracle函数之 ---- dbms_lob.substr（提取json格式报文中某个字段的值）

接口返回报文为json 格式，如下：

{"body":{"businessinfo":{"c1rate":"25.00","c2rate":"0.00","c4rate":"0.00","c5rate":"0.00","c6rate":"6.00","c8rate":"0.00","d1rate":"0.00","d2rate":"1.00","riskcode":"0355","ruleid":"R74000154606_11-0"},"vehicleinfo":{"c1rate":"4.00","c2rate":"0.00","c4rate":"0.00","c5rate":"0.00","c6rate":"6.00","c8rate":"0.00","d1rate":"0.00","d2rate":"1.00","riskcode":"0330","ruleid":"R74000154617_11-0"}},"head":{"error_code":"0000","error_message":"成功","request_type":"???","response_code":"0"}}

 

获取返回报文中，c1rate的值，用到oracle 正则查询

 

SQL

select i.c_app_no, i.c_req_content as reuqest,
i.c_res_content as response,
dbms_lob.substr( REGEXP_SUBSTR(c_res_content, '\"c1rate\":\"([0-9.]+)\"', 1, 1, 'i')) as c,
dbms_lob.substr( REGEXP_SUBSTR(REGEXP_SUBSTR(c_res_content, '\"c1rate\":\"([0-9.]+)\"', 1, 1, 'i'),'([0-9.]{2,})',1,1,'i')) as d
from WEB_INTERFACE_SOAP_CONTENT i
WHERE c_app_no = '5997499035520170000382' and
c_interface_code = 'FeeContrlRequest'

 

注：Oracle中将clob字段数据转化为字符串，采用 dbms_lob.substr