oracle 正则查询json返回报文中某个字段的值

时间:2017-08-04 19:12:16   收藏:0   阅读:1446

接口返回报文为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,
REGEXP_SUBSTR(c_res_content, ‘\"c1rate\":\"([0-9.]+)\"‘, 1, 1, ‘i‘) as c,
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‘

原文:http://www.cnblogs.com/liuyitan/p/7230491.html

评论(0
© 2014 bubuko.com 版权所有 - 联系我们:wmxa8@hotmail.com
打开技术之扣,分享程序人生!