如何使用REGEXP_SUBSTR解析数据？

小编典典

如何使用REGEXP_SUBSTR解析数据？

sql

我有一个这样的数据集（请参见下文），我尝试提取以下形式的数字：{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
;

我没有得到正确的结果集，应为以下内容

+-------------------------------------+-----------+-----------+
|                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      |
+-------------------------------------+-----------+-----------+

如何实现呢？

阅读 290

2021-04-14

共1个答案

小编典典

如果要从第二个和第三个/定界的组中获取结果，则：

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

更新：

如果只希望第一个和第二个模式匹配，并且不在乎它们在字符串中的位置，则：

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

2021-04-14