以下语句11G可用WITH T AS (select 'Z56*20141116*7*003*1^Z53*20141116*7*004*1,3^Z53*20141116*7*004*1,3^Z54*20141116*7*006*30' str from dual) SELECT LISTAGG( CASE WHEN REGEXP_LIKE(REGEXP_SUBSTR(STR,'[^/^]+',1,LEVEL),'Z53.*7.*1,3') THEN REPLACE(REGEXP_SUBSTR(STR,'[^/^]+',1,LEVEL),'1,3','71,73') ELSE REGEXP_SUBSTR(STR,'[^/^]+',1,LEVEL) END ,'^') WITHIN GROUP(ORDER BY LEVEL) from T CONNECT BY LEVEL<=REGEXP_COUNT(STR,'[^/^]+')
(select
'Z56*20141116*7*003*1^Z53*20141116*7*004*1,3^Z53*20141116*7*004*1,3^Z54*20141116*7*006*30' str
from dual)
SELECT LISTAGG(
CASE WHEN REGEXP_LIKE(REGEXP_SUBSTR(STR,'[^/^]+',1,LEVEL),'Z53.*7.*1,3')
THEN
REPLACE(REGEXP_SUBSTR(STR,'[^/^]+',1,LEVEL),'1,3','71,73')
ELSE
REGEXP_SUBSTR(STR,'[^/^]+',1,LEVEL)
END
,'^') WITHIN GROUP(ORDER BY LEVEL) from T
CONNECT BY LEVEL<=REGEXP_COUNT(STR,'[^/^]+')