首页 > 数据库技术 > 详细

oracle字符串提取记录

时间:2018-12-10 18:52:52      阅读:208      评论:0      收藏:0      [点我收藏+]

背景:需要限制用户操作次数,而用户操作次数只有统一的日志表有记录。 并且,因为在批量查询中也需做限制,所有需要一次查询多条数据,保证效率。后来采用视图做的

 

视图

instr 查找字符串,返回起始坐标, 坐标从1开始

substr切割字符串,substr(字符串,起始坐标,长度)

create or replace view v_log as
--用作批量核查时限制次数
select t2."PHONE_NUMBER",t2."TIME",t2."URL",
substr(t2.post_params, instr(t2.post_params, reportTime:)+length(reportTime:), instr(t2.post_params, ,companyName)-instr(t2.post_params, reportTime:)-length(reportTime:)) as reportTime,
substr(t2.post_params, instr(t2.post_params, companyName:)+length(companyName:), instr(t2.post_params, ,xyCode)-instr(t2.post_params, companyName:)-length(companyName:)) as companyName,
substr(t2.post_params, instr(t2.post_params, xyCode:)+length(xyCode:), instr(t2.post_params, ,zb)-instr(t2.post_params, xyCode:)-length(xyCode:)) as xyCode,
substr(t2.post_params, instr(t2.post_params, ,zb:)+length(,zb:), instr(t2.post_params, ,value)-instr(t2.post_params, ,zb:)-length(,zb:)) as zb,
substr(t2.post_params, instr(t2.post_params, ,type:)+length(,type:), length(t2.post_params)-instr(t2.post_params, ,type:)-length(,type:)) as type
from user_log t2
where t2.url like %/check/single;

 

查询结果

技术分享图片

 

oracle字符串提取记录

原文:https://www.cnblogs.com/lurenjia1994/p/10098106.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!