Oracle中拆分CLOB对话字段为坐席与客户独立列的方法

针对你需要从TEXT_RECORDS表的CONVERSATION（CLOB类型）字段中，提取并合并坐席（a:开头）和客户（c:开头）对话内容的需求，这里提供两种实用的实现方式：

方法一：递归拆分+聚合（灵活通用） #

这种方法通过递归拆分每一段对话，再用聚合函数合并，适合需要对单段对话做额外处理的场景，也能轻松应对多条记录的情况：

WITH split_conv AS (
    SELECT
        -- 提取每一段以a:或c:开头的对话内容
        TRIM(REGEXP_SUBSTR(t.CONVERSATION, '(a:|c:)(.*?)(?= a:| c:|$)', 1, level, 'n')) AS conv_part,
        -- 标记这段对话属于坐席还是客户
        CASE 
            WHEN REGEXP_SUBSTR(t.CONVERSATION, '(a:|c:)(.*?)(?= a:| c:|$)', 1, level, 'n') LIKE 'a:%' 
            THEN 'AGENT' 
            ELSE 'CUSTOMER' 
        END AS conv_type,
        t.CONVERSATION AS original_conv
    FROM TEXT_RECORDS t
    -- 递归拆分，直到所有a:/c:开头的段都被提取
    CONNECT BY LEVEL <= REGEXP_COUNT(t.CONVERSATION, '(a:|c:)')
    AND PRIOR SYS_GUID() IS NOT NULL  -- 避免递归时出现重复行
    AND PRIOR t.CONVERSATION = t.CONVERSATION
)
SELECT
    -- 合并坐席的对话内容，去掉开头的a:，用逗号分隔
    LISTAGG(CASE WHEN conv_type = 'AGENT' THEN SUBSTR(conv_part, 3) END, ', ') 
        WITHIN GROUP (ORDER BY level) AS CONV_AGENT,
    -- 合并客户的对话内容，去掉开头的c:，用逗号分隔
    LISTAGG(CASE WHEN conv_type = 'CUSTOMER' THEN SUBSTR(conv_part, 3) END, ', ') 
        WITHIN GROUP (ORDER BY level) AS CONV_CUSTOMER
FROM split_conv
GROUP BY original_conv;

关键逻辑说明： #

• REGEXP_SUBSTR的正则表达式'(a:|c:)(.*?)(?= a:| c:|$)'精准匹配每一段对话：以a:或c:开头，直到下一个a:/c:或者文本结尾为止；'n'参数让.能匹配换行符（如果对话内容有换行的话）。
• CONNECT BY LEVEL递归生成足够的行数，对应所有对话段的数量。
• LISTAGG函数按拆分顺序把同类型的对话内容合并成逗号分隔的字符串。

方法二：正则直接替换（简洁高效） #

如果不需要对单段对话做额外处理，直接用两次正则替换就能快速得到结果，性能更优：

SELECT
    -- 提取坐席对话并合并：先去掉所有客户内容，再把a:替换成逗号分隔
    REGEXP_REPLACE(
        TRIM(REGEXP_REPLACE(CONVERSATION, '(^| )c:.*?(?= a:| c:|$)', '', 1, 0, 'n')),
        '(^| )a:', ', ', 1, 0, 'n'
    ) AS CONV_AGENT,
    -- 提取客户对话并合并：先去掉所有坐席内容，再把c:替换成逗号分隔
    REGEXP_REPLACE(
        TRIM(REGEXP_REPLACE(CONVERSATION, '(^| )a:.*?(?= a:| c:|$)', '', 1, 0, 'n')),
        '(^| )c:', ', ', 1, 0, 'n'
    ) AS CONV_CUSTOMER
FROM TEXT_RECORDS;

关键逻辑说明： #

1. 内层REGEXP_REPLACE：移除所有不属于目标角色的对话内容（比如提取坐席内容时，去掉所有c:开头的段），TRIM清理首尾多余空格。
2. 外层REGEXP_REPLACE：把剩余内容里的a:/c:替换成, ，直接得到逗号分隔的合并结果。

测试你提供的示例数据，两种方法都会输出期望的结果：

内容的提问来源于stack exchange，提问作者kzmlbyrk