时间戳

首页 > 解决方案 > 如何在字符串中查找匹配模式的每种情况并作为行返回

sql - 如何在字符串中查找匹配模式的每种情况并作为行返回

问题描述

我正在尝试识别列中字符串中包含的参考号。该表如下所示：

col1  col2
1     fgREF1234fhjdREF1235hgkjREF1236
2     hREF1237hjdfREF1238djhfhs

需要编写一个 SQL 查询来识别 'REF' 后跟 4 位数字，并在各自的行中返回每个数字。

输出应如下所示：

col1  ref
1     REF1234
1     REF1235
1     REF1236
2     REF1237
2     REF1238

我努力了：

select
    case when substr(substr(col2, instr(col2, 'REF'), 7), 1, 1) like 'R'
    then substr(col2, instr(col2, 'R'), 7) else null end ref
from table

...但这只会识别字符串中的第一个匹配项。

我正在使用 Oracle SQL，但理想情况下，该解决方案能够转换为其他 SQL 变体。

任何帮助将非常感激！

标签： sqloracle

解决方案

您可以使用regexp_substr分隔符connect by level <= regexp_count(col2,'REF')（字符串中模式字符串的出现时间REFcol2）

with t(col1,col2) as
(
 select 1,'fgREF1234fhjdREF1235hgkjREF1236' from dual union all
 select 2,'hREF1237hjdfREF1238djhfhs' from dual
)   
select col1,
       regexp_substr(col2,'REF[0-9]+',1,level) as ref
  from t
connect by level <= regexp_count(col2,'REF') 
    and prior col1 = col1
    and prior sys_guid() is not null;

Demo

推荐阅读