正则表达式：检测字母表中的任何子字符串？

对于单个值,您可以使用两个connect-by子句;一个用于生成26个值,另一个用于将原始字符串拆分为单个字符.由于ASCII码是连续的,因此ascii()函数可用于为每个存在的字符生成1-26的数字.然后左连接两个列表：

var str varchar2(26);
exec :str := 'ABCDFGZ';

with alphabet as (
  select level as pos
  from dual connect by level <= 26
),chars as (
  select substr(:str,level,1) character,ascii(substr(:str,1)) - 64 as pos
  from dual connect by level <= length(:str)
)
select listagg(nvl(chars.character,' '))
  within group (order by alphabet.pos) as result
from alphabet
left outer join chars on chars.pos = alphabet.pos;

RESULT                   
--------------------------
ABCD FG                  Z

这是一个SQL * Plus绑定变量,以避免重复该字符串,但它可以从其他地方插入.

创建视图有点复杂,因为表中的多行可能会导致连接问题.可能值列表必须包括表中的主键(或至少唯一键),以及原始字符串(如果要包含该键(以及表中所需的任何其他列)).拆分列表还需要包含主键,并且需要包含在外部联接中.

create view v42 as
with possible as (
  select id,str,level as pos
  from t42
  connect by level <= 26
  and prior id = id
  and prior sys_guid() is not null
),actual as (
  select id,substr(str,ascii(substr(str,1)) - 64 as pos
  from t42
  connect by level <= length(str)
  and prior id = id
  and prior sys_guid() is not null
)
select possible.id,possible.str,listagg(nvl(actual.character,' '))
  within group (order by possible.pos) as result
from possible
left outer join actual on actual.id = possible.id and actual.pos = possible.pos
group by possible.id,possible.str;

然后使用一些样本数据,从v42中选择*给出：

ID STR                        RESULT                   
---------- -------------------------- --------------------------
         1 A                          A                          
         2 Z                                                   Z 
         3 AZ                         A                        Z 
         4 ABCDFGZ                    ABCD FG                  Z 
         5 ABCDEGHIJKLMNOPQTUVWXYZ    ABCDE GHIJKLMNOPQ  TUVWXYZ

SQL Fiddle demo.

使用递归CTE可能会更清洁一些.或者使用一次处理一个值的函数.或者使用正则表达式,当然……

Here’s a recursive CTE version,为了好玩：

create view v42 as
with possible(id,pos,character) as (
  select id,1,'A'
  from t42
  union all
  select id,pos + 1,chr(64 + pos + 1)
  from possible
  where pos < 26
),actual (id,1)
  from t42
  union all
  select id,1)
  from actual
  where pos < length(str)
)
select possible.id,' '))
  within group (order by possible.pos) as result
from possible
left outer join actual
on actual.id = possible.id
and actual.character = possible.character
group by possible.id,possible.str;

(SQL Fiddle使用间距做奇怪的事情,因此从“运行SQL”下拉列表中查看纯文本输出.)