Oracle LISTAGG函数溢出时如何在开头截断并添加省略号？

Oracle原生的LISTAGG函数的ON OVERFLOW TRUNCATE子句仅支持将截断标记追加在拼接内容的末尾，无法直接实现把标记放在开头的需求。不过可以通过嵌套查询结合字符串/LOB处理函数来达成目标。

解决方案（12cR2及以上版本） #

利用LISTAGG ... ON OVERFLOW TO CLOB先生成完整的拼接内容（转为CLOB避免中途截断），再在外部查询中判断长度，若超过4000字符则截取末尾的3997个字符，前置'...'以保证总长度为4000：

SELECT
    id,
    CASE
        WHEN DBMS_LOB.GETLENGTH(agg_content) > 4000 
            THEN '...' || DBMS_LOB.SUBSTR(agg_content, 3997, DBMS_LOB.GETLENGTH(agg_content) - 3996)
        ELSE DBMS_LOB.SUBSTR(agg_content, 4000)
    END AS truncated_agg_content
FROM (
    SELECT
        id,
        LISTAGG(content, ',') WITHIN GROUP (ORDER BY content) ON OVERFLOW TO CLOB AS agg_content
    FROM test_table
    GROUP BY id
) t;

低版本兼容方案（12cR2以下） #

如果你的Oracle版本不支持LISTAGG的溢出处理，可以用XMLAGG生成CLOB格式的拼接结果，再做同样的截断处理：

SELECT
    id,
    CASE
        WHEN DBMS_LOB.GETLENGTH(agg_content) > 4000 
            THEN '...' || DBMS_LOB.SUBSTR(agg_content, 3997, DBMS_LOB.GETLENGTH(agg_content) - 3996)
        ELSE DBMS_LOB.SUBSTR(agg_content, 4000)
    END AS truncated_agg_content
FROM (
    SELECT
        id,
        RTRIM(XMLAGG(XMLELEMENT(e, content, ',').EXTRACT('//text()') ORDER BY content).GetClobVal(), ',') AS agg_content
    FROM test_table
    GROUP BY id
) t;

关键说明 #

• 计算逻辑：'...'占3个字符，因此需要从完整拼接内容的末尾截取4000 - 3 = 3997个字符，起始位置为总长度 - 3997 + 1，确保截取的是最后一段内容。
• 自定义标记：如果使用其他截断标记（如'[已截断]'），需调整截取的字符数为4000 - LENGTH(标记)，同时对应修改起始位置的计算。

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