oracle9i 拆分:字母加数字的字符串

Question

SELECT 'A1BC1D' FROM DUAL;
目的
A1
B
C1
D
用于查询其它表
select * from a where a.name='A1';
select * from a where a.name='B';
select * from a where a.name='C1';
select * from a where a.name='D';

Answer 1

9i貌似不支持正则呢。
规律是什么，比如出现A12BC1D的情况如何提取

Answer 2

SELECT replace(MAX(SYS_CONNECT_BY_PATH(val, ' ')), ' ', '')
  FROM (select val,
               flag,
               ROW_NUMBER() OVER(PARTITION BY flag ORDER BY flag) RN2
          from (select val,
                       (case
                         when ascii(val) <= 57 then
                          0
                         else
                          1
                       end) + rn flag,
                       rn
                  from (select substr('CA1BC1D', rownum, 1) val, rownum rn
                          from dual
                        connect by rownum <= length('CA1BC1D'))))
 START WITH RN2 = 1
CONNECT BY PRIOR flag = flag
       AND PRIOR RN2 = RN2 - 1
 GROUP BY flag
 order by flag