转载

查询字符串中连续出现的字符

根据用户需求需要查询字符串中连续出现的字符

如'mn,asdfs,opq,dfasf,abc,asdlfj,defg' 'DF,RST'，要求找出两个字符串中连续出现的字符

这里两个字符串数据相当于表中的两行数据，具体数据用sql构造。对于这种需求的sql主要涉及两块，一个是如何找出字符串中被，号

分割的字符。一个是如何判断字符是连续的字符。

1 找出字符串中所有被，号分割出的字符

SELECT regexp_substr(teststr,'[^,]+',1,level) teststr

FROM (

SELECT 'mn,asdfs,opq,dfasf,abc,asdlfj,defg' teststr

,1 id1

FROM dual

UNION ALL

SELECT 'DF,RST' teststr

,2 id1

FROM DUAL) t

connect by level <20

AND PRIOR id1 = id1

AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;

这里第一行数据和第二行数据明显产生递归关系，因此需要PRIOR id1 = id1 来过滤不必要的递归步骤

2 而查询字符是否连续的，可以用如下sql查询出所有字符的组合

select ss

from (select replace(sys_connect_by_path(c, ','), ',') ss

from (select rownum i, chr(65 + rownum - 1) c

from dual

connect by rownum <= 26)

connect by prior i = i - 1)

where length(ss) >= 2;

3 最后用分割出的每个字符单元和连续字符的组合判断，查询出所有属于连续字符的数据即所要结果

select teststr,

regexp_substr(upper(teststr), '[A-Z]{2,10}') reqstr

   from ( SELECT regexp_substr(teststr,'[^,]+',1,level) teststr
          FROM (
                SELECT 'mn,asdfs,opq,dfasf,abc,asdlfj,defg' teststr
                      ,1 id1
                FROM dual
                UNION ALL
                SELECT 'DF,RST' teststr
                      ,2 id1
                FROM DUAL) t
          connect by level <20
          AND PRIOR id1 = id1
          AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
         )

where regexp_substr(upper(teststr), '[A-Z]{2,10}') in

(select ss

from (select replace(sys_connect_by_path(c, ','), ',') ss

from (select rownum i, chr(65 + rownum - 1) c

from dual

connect by rownum <= 26)

connect by prior i = i - 1)

where length(ss) >= 2);

正文到此结束