如何使用REGEXP_SUBSTR解析数据? [英] How to parse data using REGEXP_SUBSTR?
本文介绍了如何使用REGEXP_SUBSTR解析数据?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个这样的数据集(请参见下文),我尝试提取以下形式的数字:{variable_number_of_digits} {连字符} {only_one_digit}:
I have a data set like this (see below) and I try to extract digits which are in form {variable_number_of_digits}{hyphen}{only_one_digit}:
with mcte as (
select 'ILLD/ELKJS/00000000/ELKJS/FHSH' as addr from dual
union all
select 'ILLD/EFECTE/0116988-7-002/ADFA/ADFG' as addr from dual
union all
select 'IIODK/1573230-0/2216755-7/' as addr from dual
union all
select 'IIODK/1573230-0/2216755-700/WRITE' as addr from dual
)
select addr,
REGEXP_SUBSTR(addr,'(\/)([0-9-]+)',1,1,NULL,2) AS num1,
REGEXP_SUBSTR(addr,'(\/)([^\/]+\/)([0-9\-]+)',1,1,NULL,3) num2
from mcte
;
我没有得到正确的结果集,应该是以下内容
I'm not getting a correct results set which should be the following
+-------------------------------------+-----------+-----------+
| ADDR | NUM1 | NUM2 |
+-------------------------------------+-----------+-----------+
| ILLD/ELKJS/00000000/ELKJS/FHSH | NULL | NULL |
| ILLD/EFECTE/0116988-7-002/ADFA/ADFG | NULL | NULL |
| IIODK/1573230-0/2216755-7/ | 1573230-0 | 2216755-7 |
| IIODK/1573230-0/2216755-700/WRITE | 1573230-0 | NULL |
+-------------------------------------+-----------+-----------+
如何实现?
推荐答案
如果要从第二个和第三个/
分隔的组中获取结果,则:
If you want to get the results from the second and third /
delimited groups then:
with mcte ( addr ) as (
select 'ILLD/ELKJS/00000000/ELKJS/FHSH' from dual union all
select 'ILLD/EFECTE/0116988-7-002/ADFA/ADFG' from dual union all
select 'IIODK/1573230-0/2216755-7/' from dual union all
select 'IIODK/1573230-0/2216755-700/WRITE' from dual union all
select 'IIODK/TEST/1573230-0/2216755-700/WRITE' from dual
)
select addr,
REGEXP_SUBSTR(addr,'^[^/]*/(\d+-\d)/',1,1,NULL,1) AS num1,
REGEXP_SUBSTR(addr,'^[^/]*/[^/]*/(\d+-\d)/',1,1,NULL,1) num2
from mcte;
输出:
ADDR NUM1 NUM2
-------------------------------------- ------------------- -------------------
ILLD/ELKJS/00000000/ELKJS/FHSH
ILLD/EFECTE/0116988-7-002/ADFA/ADFG
IIODK/1573230-0/2216755-7/ 1573230-0 2216755-7
IIODK/1573230-0/2216755-700/WRITE 1573230-0
IIODK/TEST/1573230-0/2216755-700/WRITE 1573230-0
更新:
如果只希望第一个和第二个模式匹配,并且不在乎它们在字符串中的位置,则:
If you just want the first and second pattern that match and do not care where they are in the string then:
with mcte ( addr ) as (
select 'ILLD/ELKJS/00000000/ELKJS/FHSH' from dual union all
select 'ILLD/EFECTE/0116988-7-002/ADFA/ADFG' from dual union all
select 'IIODK/1573230-0/2216755-7/' from dual union all
select 'IIODK/1573230-0/2216755-700/WRITE' from dual union all
select 'IIODK/TEST/1573230-0/2216755-700/WRITE' from dual union all
select '1234567-8' from dual union all
select '1234567-8/9876543-2' from dual union all
select '1234567-8/TEST/9876543-2' from dual
)
select addr,
REGEXP_SUBSTR(addr,'(^|/)(\d+-\d)(/|$)',1,1,NULL,2) AS num1,
REGEXP_SUBSTR(addr,'(^|/)\d+-\d(/.+?)?/(\d+-\d)(/|$)',1,1,NULL,3) num2
from mcte;
输出:
ADDR NUM1 NUM2
-------------------------------------- ------------------- ------------------
ILLD/ELKJS/00000000/ELKJS/FHSH
ILLD/EFECTE/0116988-7-002/ADFA/ADFG
IIODK/1573230-0/2216755-7/ 1573230-0 2216755-7
IIODK/1573230-0/2216755-700/WRITE 1573230-0
IIODK/TEST/1573230-0/2216755-700/WRITE 1573230-0
1234567-8 1234567-8
1234567-8/9876543-2 1234567-8 9876543-2
1234567-8/TEST/9876543-2 1234567-8 9876543-2
这篇关于如何使用REGEXP_SUBSTR解析数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文