我假设最后一个逗号是模板字符串和分隔术语之间的分隔符。您可以使用递归子查询分解子句和简单的字符串函数：
WITH terms ( value, terms, num_terms ) AS ( SELECt SUBSTr( value, 1, INSTR( value, ', ', -1 ) - 1 ), SUBSTr( value, INSTR( value, ', ', -1 ) + 2 ), REGEXP_COUNT(SUBSTr( value, INSTR( value, ', ', -1 ) + 2 ),'.+?(| |$)' ) FROM table_name),term_bounds ( rn, value, terms, start_pos, lvl ) AS ( SELECt ROWNUM, REPLACE(value,'%' || num_terms,CASE num_termsWHEN 1THEN termsELSE SUBSTr( terms, INSTR( terms, '| ', 1, num_terms - 1 ) + 2 )END ), terms, CASE WHEN num_terms > 1 THEN INSTR( terms, '| ', 1, num_terms - 1 ) ELSE 1 END, num_terms FROM termsUNIOn ALL SELECt rn, REPLACE(value,'%' || (lvl - 1),CASE lvl - 1WHEN 1THEN SUBSTr( terms, 1, start_pos - 1 )ELSE SUBSTr( terms, INSTR( terms, '| ', 1, lvl - 2 ) + 2, start_pos - INSTR( terms, '| ', 1, lvl - 2 ) - 2 )END ), terms, CASE WHEN lvl > 2 THEN INSTR( terms, '| ', 1, lvl - 2 ) ELSE 1 END, lvl - 1 FROM term_bounds WHERe lvl > 1)SEARCH DEPTH FIRST BY rn SET rn_orderSELECt valueFROM term_boundsWHERe lvl = 1;
Which, for the sample data:
CREATE TABLE table_name ( value ) ASSELECt '%1||'-'||%2||%3, SITE_NO| SITE_NAME| COUNTRY' FROM DUAL UNIOn ALLSELECt '0.1 * %1, WIND_RES' FROM DUAL UNIOn ALLSELECt '%1, TOTAL' FROM DUAL UNIOn ALLSELECt 'CASE WHEN LENGTH(%1) < 8 THEN NULL ELSE TO_DATE(%1,'yyyymmdd')END, MIN_DATE' FROM DUAL UNIOn ALLSELECt '%1(+)=%3 and %2(+)=%4, ABC| LMN| PQR| XYZ' FROM DUAL UNIOn ALLSELECt '%1, %2, %3, %4, %5, %6, %7, %8, %9, %10, %11, ONE| TWO| THREE| FOUR| FIVE| SIX| SEVEN| EIGHT| NINE| TEN| ELEVEN' FROM DUAL UNIOn ALLSELECt '%%%%%%%7, HELLO| 1| 2| 3| 4| 5| 6' FROM DUAL
Outputs:
| VALUE || :----------------------------------------------------------------------------------------- || SITE_NO||'-'||SITE_NAME||COUNTRY || 0.1 * WIND_RES|| TOTAL || CASE WHEN LENGTH(MIN_DATE) < 8 THEN NULL ELSE TO_DATE(MIN_DATE,'yyyymmdd')END || ABC(+)=PQR and LMN(+)=XYZ|| ONE, TWO, THREE, FOUR, FIVE, SIX, SEVEN, EIGHT, NINE, TEN, ELEVEN || HELLO
