您可以同时使用regexp_substr()和replace()函数：

select replace( str,regexp_substr(str,'(<column width="5%")(.*)(</url></column>)')
              ) as "Extracted String"
  from tab 

其中tab表示原始表，而str的表中包含共享的长字符串数据。

Demo

您可以结合使用INSTR和SUBSTR函数，如下所述：

WITH YOUR_TABLE(YOUR_COL) AS
(SELECT
    q'{<listdef page="25" default_order="entity" default_order_type="a"><reportId>1332</reportId><name>test</name><description>test</description><multiProduct>N</multiProduct><product_code>BG,</product_code><equivalentCurrency>EUR</equivalentCurrency><use_absolute_width>Y</use_absolute_width><column name="ref_id"><description locale="fr">Système Id</description></column><column name="iss_date"><description locale="fr">Date d'émission</description></column><column name="exp_date"><description locale="fr">Date de Validité</description></column><column name="bg_amt"><description locale="fr">Montant de la Garantie Bancaire</description></column><column name="applicant_name"><description locale="fr">Nom du donneur d'ordre</description></column><column name="entity"><description locale="fr">Entité</description></column><column name="applicant_reference"><description locale="fr">Référence du donneur d'ordre</description></column><column width="5%" name="action"><url action="y"><parameter column="ref_id" name="REFERENCE_ID"/><parameter column="product_code" name="PRODUCT_CODE"/><parameter binding_code="HISTORY" name="OPTION_CODE"/><parameter binding_code="LIST_INQUIRY" name="OPERATION_CODE"/><parameter value="TRADEADMIN_SCREEN" name="SCREEN"/></url></column><column name="bg_liab_amt" hidden="y"/><column name="ref_id" hidden="y"/><column name="product_code" hidden="y"/><column name="ref_id" hidden="y"/><parameter input="y" name="ENTITE" size="" max_length=""><description locale="fr">ENTITE</description></parameter><parameter input="y" name="TIERS" size="" max_length=""><description locale="fr">TIERS</description></parameter><parameter name="export_list" file="stock CBC" type="export" file_name="stock CBC"/><candidate name="BG"><filter><criteria><column name="entity" type="String"/><operator type="like"/><value type="parameter">ENTITE</value></criteria><criteria><column name="applicant_reference" type="String"/><operator type="like"/><value type="parameter">TIERS</value></criteria></filter></candidate><aggregate type="count"><column name="ref_id"/><description locale="fr">Nombre de dossiers</description></aggregate><aggregate type="sum" cur="EUR"><column name="bg_liab_amt"/><description locale="fr">Total encours</description></aggregate><executable_flag>Y</executable_flag></listdef>}'
FROM DUAL)
SELECT
    SUBSTR(YOUR_COL,1,STARTPOS) || SUBSTR(YOUR_COL,ENDPOS) AS OUTPUT
FROM (
        SELECT
            YOUR_COL,INSTR(YOUR_COL,'<column width="5%" name="action">') - 1 AS STARTPOS,'name="SCREEN"/></url></column>') + 30 AS ENDPOS
        FROM  YOUR_TABLE
    );

Demo

干杯！

如果您的值在语法上是有效的XML / XHTML，则可以将其转换为XMLTYPE并使用DELETEXML删除column标签，其name属性等于"action"：

Oracle设置：

CREATE TABLE test_data ( value ) AS
SELECT q'{<listdef page="25" default_order="entity" default_order_type="a"><reportId>1332</reportId><name>test</name><description>test</description><multiProduct>N</multiProduct><product_code>BG,</product_code><equivalentCurrency>EUR</equivalentCurrency><use_absolute_width>Y</use_absolute_width><column name="ref_id"><description locale="fr">Système Id</description></column><column name="iss_date"><description locale="fr">Date d'émission</description></column><column name="exp_date"><description locale="fr">Date de Validité</description></column><column name="bg_amt"><description locale="fr">Montant de la Garantie Bancaire</description></column><column name="applicant_name"><description locale="fr">Nom du donneur d'ordre</description></column><column name="entity"><description locale="fr">Entité</description></column><column name="applicant_reference"><description locale="fr">Référence du donneur d'ordre</description></column><column width="5%" name="action"><url action="y"><parameter column="ref_id" name="REFERENCE_ID"/><parameter column="product_code" name="PRODUCT_CODE"/><parameter binding_code="HISTORY" name="OPTION_CODE"/><parameter binding_code="LIST_INQUIRY" name="OPERATION_CODE"/><parameter value="TRADEADMIN_SCREEN" name="SCREEN"/></url></column><column name="bg_liab_amt" hidden="y"/><column name="ref_id" hidden="y"/><column name="product_code" hidden="y"/><column name="ref_id" hidden="y"/><parameter input="y" name="ENTITE" size="" max_length=""><description locale="fr">ENTITE</description></parameter><parameter input="y" name="TIERS" size="" max_length=""><description locale="fr">TIERS</description></parameter><parameter name="export_list" file="stock CBC" type="export" file_name="stock CBC"/><candidate name="BG"><filter><criteria><column name="entity" type="String"/><operator type="like"/><value type="parameter">ENTITE</value></criteria><criteria><column name="applicant_reference" type="String"/><operator type="like"/><value type="parameter">TIERS</value></criteria></filter></candidate><aggregate type="count"><column name="ref_id"/><description locale="fr">Nombre de dossiers</description></aggregate><aggregate type="sum" cur="EUR"><column name="bg_liab_amt"/><description locale="fr">Total encours</description></aggregate><executable_flag>Y</executable_flag></listdef>}' FROM DUAL

查询：

SELECT DELETEXML( XMLTYPE( value ),'/listdef/column[@name="action"]' ).getStringVal()
FROM   test_data;

输出：

| DELETEXML(XMLTYPE(VALUE),'/LISTDEF/COLUMN[@NAME="ACTION"]').GETSTRINGVAL()                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| <listdef page="25" default_order="entity" default_order_type="a"><reportId>1332</reportId><name>test</name><description>test</description><multiProduct>N</multiProduct><product_code>BG,</product_code><equivalentCurrency>EUR</equivalentCurrency><use_absolute_width>Y</use_absolute_width><column name="ref_id"><description locale="fr">Syst??me Id</description></column><column name="iss_date"><description locale="fr">Date d&apos;??mission</description></column><column name="exp_date"><description locale="fr">Date de Validit??</description></column><column name="bg_amt"><description locale="fr">Montant de la Garantie Bancaire</description></column><column name="applicant_name"><description locale="fr">Nom du donneur d&apos;ordre</description></column><column name="entity"><description locale="fr">Entit??</description></column><column name="applicant_reference"><description locale="fr">R??f??rence du donneur d&apos;ordre</description></column><column name="bg_liab_amt" hidden="y"/><column name="ref_id" hidden="y"/><column name="product_code" hidden="y"/><column name="ref_id" hidden="y"/><parameter input="y" name="ENTITE" size="" max_length=""><description locale="fr">ENTITE</description></parameter><parameter input="y" name="TIERS" size="" max_length=""><description locale="fr">TIERS</description></parameter><parameter name="export_list" file="stock CBC" type="export" file_name="stock CBC"/><candidate name="BG"><filter><criteria><column name="entity" type="String"/><operator type="like"/><value type="parameter">ENTITE</value></criteria><criteria><column name="applicant_reference" type="String"/><operator type="like"/><value type="parameter">TIERS</value></criteria></filter></candidate><aggregate type="count"><column name="ref_id"/><description locale="fr">Nombre de dossiers</description></aggregate><aggregate type="sum" cur="EUR"><column name="bg_liab_amt"/><description locale="fr">Total encours</description></aggregate><executable_flag>Y</executable_flag></listdef> |

db 提琴here