SQL> with T AS (SELECT '某小区11栋2单元1101号' STR FROM DUAL) 2 SELECT LISTAGG(STR,'')WITHIN GROUP(ORDER BY N) 3 FROM( 4 SELECT 'Y' AS FLAG, 5 REGEXP_INSTR(STR,'[0-9]+',1,ROWNUM) N, 6 TO_CHAR(TO_NUMBER(REGEXP_SUBSTR(STR,'[0-9]+',1,ROWNUM))+1) STR 7 FROM T CONNECT BY ROWNUM<=REGEXP_COUNT(STR,'[0-9]+') 8 UNION ALL 9 SELECT 'N' AS FLAG, 10 REGEXP_INSTR(STR,'[^0-9]+',1,ROWNUM) N, 11 REGEXP_SUBSTR(STR,'[^0-9]+',1,ROWNUM) STR 12 FROM T CONNECT BY ROWNUM<=REGEXP_COUNT(STR,'[^0-9]+'));LISTAGG(STR,'')WITHINGROUP(ORDERBYN) -------------------------------------------------------------------------------- 某小区12栋3单元1102号SQL>
2 SELECT LISTAGG(STR,'')WITHIN GROUP(ORDER BY N)
3 FROM(
4 SELECT 'Y' AS FLAG,
5 REGEXP_INSTR(STR,'[0-9]+',1,ROWNUM) N,
6 TO_CHAR(TO_NUMBER(REGEXP_SUBSTR(STR,'[0-9]+',1,ROWNUM))+1) STR
7 FROM T CONNECT BY ROWNUM<=REGEXP_COUNT(STR,'[0-9]+')
8 UNION ALL
9 SELECT 'N' AS FLAG,
10 REGEXP_INSTR(STR,'[^0-9]+',1,ROWNUM) N,
11 REGEXP_SUBSTR(STR,'[^0-9]+',1,ROWNUM) STR
12 FROM T CONNECT BY ROWNUM<=REGEXP_COUNT(STR,'[^0-9]+'));LISTAGG(STR,'')WITHINGROUP(ORDERBYN)
--------------------------------------------------------------------------------
某小区12栋3单元1102号SQL>