-------修改一下 SQL> SQL> select * 2 from (select regexp_substr('10011110000010101111101010111', 3 '[^0]+', 4 1, 5 rownum) sr 6 from dual 7 connect by rownum <= 10) 8 where length(sr) > 1 9 ;SR -------------------------------------------------------------------------------- 1111 11111 111SQL>
WITH tab AS(SELECT '10011110000010101111101010111' id FROM dual) SELECT REPLACE(wm_concat(id),',','') id FROM( SELECT LEVEL rn,SubStr(id,LEVEL,1) id FROM tab CONNECT BY LEVEL<=Length(id) ) WHERE id=1 GROUP BY rn-rownum HAVING Count(*)>1 ID -------- 1111 11111 111
SQL>
SQL> select regexp_substr('10011110000010101111101010111', '[^0]+', 1, rownum)
2 from dual
3 connect by rownum <= 10
4 ;REGEXP_SUBSTR('100111100000101
--------------------------------------------------------------------------------
1
1111
1
1
11111
1
1
11110 rows selectedSQL>
-------修改一下
SQL>
SQL> select *
2 from (select regexp_substr('10011110000010101111101010111',
3 '[^0]+',
4 1,
5 rownum) sr
6 from dual
7 connect by rownum <= 10)
8 where length(sr) > 1
9 ;SR
--------------------------------------------------------------------------------
1111
11111
111SQL>
WITH tab AS(SELECT '10011110000010101111101010111' id FROM dual)
SELECT REPLACE(wm_concat(id),',','') id FROM(
SELECT LEVEL rn,SubStr(id,LEVEL,1) id FROM tab CONNECT BY LEVEL<=Length(id)
)
WHERE id=1
GROUP BY rn-rownum HAVING Count(*)>1
ID
--------
1111
11111
111