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

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

如'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);