根据用户需求需要查询字符串中连续出现的字符
如'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);
