看到题目第一个想法 但是效率很一般 肯定还有其他方法的.. with t1 as ( select 1 tid,'23' tno from dual union all select 2 tid,'35' tno from dual union all select 3 tid,'567' tno from dual union all select 4 tid,'745' tno from dual union all select 5 tid,'6' tno from dual union all select 6 tid,'555' tno from dual union all select 7 tid,'7891' tno from dual union all select 8 tid,'12345' tno from dual union all select 9 tid,'123342313' tno from dual union all select 10 tid,'2468' tno from dual )select distinct tid,tno from ( select distinct tid,tno,substr(tno,level+1,1)-substr(tno,level,1) c1 from t1 where length(tno) between 2 and 9 connect by level < length(tno) order by tid ) where c1 <> 1 order by tid tid tno -------------------------------- 1 2 35 2 4 745 3 6 555 4 7 7891 5 9 123342313 6 10 2468
上面掉了一个 connect by level < length(tno)+1
select * from t where (length(id) between 2 and 10 and not regexp_like('0123456789',t.tno)) or length(id)>=11
with t1 as
(
select 1 tid,'23' tno from dual union all
select 2 tid,'35' tno from dual union all
select 3 tid,'567' tno from dual union all
select 4 tid,'745' tno from dual union all
select 5 tid,'6' tno from dual union all
select 6 tid,'555' tno from dual union all
select 7 tid,'7891' tno from dual union all
select 8 tid,'12345' tno from dual union all
select 9 tid,'123342313' tno from dual union all
select 10 tid,'2468' tno from dual
)select distinct tid,tno
from
(
select distinct tid,tno,substr(tno,level+1,1)-substr(tno,level,1) c1
from t1
where length(tno) between 2 and 9
connect by level < length(tno)
order by tid
)
where c1 <> 1
order by tid
tid tno
--------------------------------
1 2 35
2 4 745
3 6 555
4 7 7891
5 9 123342313
6 10 2468
connect by level < length(tno)+1
and not regexp_like('0123456789',t.tno))
or length(id)>=11