正则表达式问题,顺便散分喽!

jdsnhan 2018-03-20 09:07:37
正则表达式功能很强大,可惜,一直不能领悟其精髓,只会简单的使用。现有一需求,感觉可以用正则实现,但总有缺陷。
with a as
(select '001' code,1 feature_order, '(' feature_left,'~' feature_right from dual
union all
select '001' code,2 feature_order, '' feature_left,')×' feature_right from dual
union all
select '001' code,3 feature_order, '' feature_left,'' feature_right from dual
union all
select '002' code,1 feature_order, 'φ' feature_left,'×' feature_right from dual
union all
select '002' code,2 feature_order, '' feature_left,'~' feature_right from dual
union all
select '002' code,3 feature_order, 'φ' feature_left,'×' feature_right from dual
union all
select '002' code,4 feature_order, '' feature_left,'' feature_right from dual
union all
select '003' code,1 feature_order, '' feature_left,'^' feature_right from dual
union all
select '003' code,2 feature_order, '' feature_left,' ' feature_right from dual --此处分隔符是空格
union all
select '003' code,3 feature_order, '' feature_left,'^' feature_right from dual
union all
select '003' code,4 feature_order, '' feature_left,'' feature_right from dual
),
b as
(select '001' code,'(30~20)×5' title from dual
union all
select '002' code,'φ2.9×1.78~φ28.17×3.53' title from dual
union all
select '003' code,'18L/桶^GL-4 85W/90^厦工专用' title from dual
)
select a.*,b.* from a,b
where a.code = b.code

A.B两个表,A表存储了特征分隔符,B表存储特征描述。
现在想把特征描述按照指定的特征分隔符拆分出队列的值。
请注意:
1、有的时候有左分隔符,有时候没有
2、右分隔符有的时候是空格
3、分隔符不一定可能是多位,不一定是一位。
如图 :
...全文
569 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
  • 打赏
  • 举报
回复
不用游标的话咋弄哦 ,看看高手操作
jdsnhan 2018-03-20
  • 打赏
  • 举报
回复
摧毁沙发。不要用游标逐行截取的方法,那个我已经写出来了,想学习一下正则。
卖水果的net 2018-03-20
  • 打赏
  • 举报
回复
mark,学习一下。
花开了叫我 2018-03-20
  • 打赏
  • 举报
回复
with a as (select '001' code,1 feature_order, '(' feature_left,'~' feature_right from dual union all select '001' code,2 feature_order, '' feature_left,')×' feature_right from dual union all select '001' code,3 feature_order, '' feature_left,'' feature_right from dual union all select '002' code,1 feature_order, 'φ' feature_left,'×' feature_right from dual union all select '002' code,2 feature_order, '' feature_left,'~' feature_right from dual union all select '002' code,3 feature_order, 'φ' feature_left,'×' feature_right from dual union all select '002' code,4 feature_order, '' feature_left,'' feature_right from dual union all select '003' code,1 feature_order, '' feature_left,'^' feature_right from dual union all select '003' code,2 feature_order, '' feature_left,' ' feature_right from dual --此处分隔符是空格 union all select '003' code,3 feature_order, '' feature_left,'^' feature_right from dual union all select '003' code,4 feature_order, '' feature_left,'' feature_right from dual ), b as (select '001' code,'(30~20)×5' title from dual union all select '002' code,'φ2.9×1.78~φ28.17×3.53' title from dual union all select '003' code,'18L/桶^GL-4 85W/90^厦工专用' title from dual ) , a1 AS ( SELECT CODE,max(feature_order) feature_order,replace(listagg(regexp_replace(feature_left,'(\S)','\\\1')||'(.*?)'||regexp_replace(feature_right,'(\S)','\\\1'),CHR(10))WITHIN GROUP(ORDER BY feature_order),CHR(10),'')||'$' match FROM a GROUP BY CODE) SELECT CODE,title,level,regexp_substr(title,match,1,1,NULL,LEVEL) FROM ( SELECT b.code,title,feature_order,match FROM b,a1 WHERE b.code=a1.code) CONNECT BY PRIOR CODE=CODE AND PRIOR dbms_random.value IS NOT NULL AND LEVEL<=feature_order --给出一种做法 方法有很多种
jdsnhan 2018-03-20
  • 打赏
  • 举报
回复
引用 2 楼 baidu_36457652 的回复:
不用游标的话咋弄哦 ,看看高手操作
我感觉可以的,如果只考虑右分隔符,不考虑左分隔符,就正确,但是如何左右分隔符同时兼顾,我就不会了。

17,075

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧