oracle从列中获取所有匹配的事件 [英] oracle get all matched occurrences from a column
本文介绍了oracle从列中获取所有匹配的事件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个表,该表有2列:ID& JOB_Description(文字). 我想编写一个oracle SQL来提取中的所有子字符串 与常规模式匹配的描述列.
I have a table, which has 2 columns: ID & JOB_Description(Text). I would like to write an oracle SQL to extract all substrings in the Description column which match a regular pattern.
但是,我已经学会了如何使用下面的SQL从字符串中提取匹配的子字符串,但是我不知道一次在上述表(列:JOB_Description)上一次对所有数据使用下面的SQL.
However, I have learnt how to extract matched substrings from a string with below SQL, but I have no idea to apply below SQL on all data in one go on the aforementioned table(column:JOB_Description).
SQL从字符串中获取所有匹配的出现:
SQL to get all matched occurrences from a string:
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
可以在此处
这篇关于oracle从列中获取所有匹配的事件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文