我有一个表,其具有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}');
你可以试试这个查询出来。
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
最后两行的一个很好的解释可以发现这里