甲骨文获得所有匹配的事件从一列(oracle get all matched occurrences

2019-10-28 12:08发布

我有一个表,其具有2列:ID&JOB_Description(文本)。 我想编写一个oracle SQL提取说明列匹配规则的图案,所有子。

不过,我已经学会了如何从SQL以下字符串中提取匹配子,但我不知道要应用SQL下面的所有数据一次性对上述表(列:JOB_Description)。

SQL获得字符串中的所有匹配的事件:

 SELECT REGEXP_SUBSTR(JOB_Description, '(ABC|DE)([[:digit:]]){5}', 1, LEVEL) AS substr
   FROM (
         select 'Please help to repair ABC12345, DE22222' as JOB_Description 
          from DUAL)
  CONNECT BY LEVEL <= REGEXP_COUNT(JOB_Description, '(ABC|DE)([[:digit:]]){5}');

Answer 1:

你可以试试这个查询出来。

 with test as(
  select 'ABC12345, DE22222' as JOB_Description from DUAL union
  select 'Please help to repair ABC12345, DE22222' as JOB_Description from DUAL 
)
SELECT REGEXP_SUBSTR(JOB_Description, '(ABC|DE)([[:digit:]]){5}', 1, LEVEL) AS substr
FROM test
CONNECT BY LEVEL <= REGEXP_COUNT(JOB_Description, '(ABC|DE)([[:digit:]]){5}')
  AND PRIOR JOB_Description = JOB_Description
  AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL

结果:

ABC12345
DE22222
ABC12345
DE22222

最后两行的一个很好的解释可以发现这里



文章来源: oracle get all matched occurrences from a column