Oracle 19c如何用REGEXP_REPLACE将CRLF替换为字面量\r\n

核心问题说明 #

Windows风格换行符是回车CHR(13)+换行CHR(10)的连续组合，之前的写法失败原因可归为三类：

• 仅替换了单个控制字符（CR或LF），残留的另一个控制字符会保留实际换行效果
• 字符串拼接语法错误，两个CHR()函数之间缺少拼接符||
• 正则替换未开启全局匹配参数，且Oracle正则不会直接识别字符串中书写的\r\n为控制字符，仅会将其作为普通字母r、n处理

正向转换（数据库导出到JSON：CRLF转字面量\r\n） #

优先使用普通REPLACE函数，性能远高于正则替换，单条语句即可完成：

-- 通用兼容写法：先匹配成对CRLF，再兜底处理单独存在的CR、LF，适配不同来源的换行格式
SELECT REPLACE(
            REPLACE(v_text, CHR(13)||CHR(10), '\r\n'),
            CHR(13), '\r\n'
       ), CHR(10), '\r\n')
INTO v_outtext
FROM dual;

如果确认数据中换行严格为CR+LF成对出现的标准Windows格式，可直接简化为：

SELECT REPLACE(v_text, CHR(13)||CHR(10), '\r\n')
INTO v_outtext
FROM dual;

如果需要使用正则写法，注意必须传入第5个参数0开启全局匹配（Oracle中REGEXP_REPLACE默认仅替换第一个匹配项）：

SELECT REGEXP_REPLACE(v_text, CHR(13)||'?'||CHR(10)||'|'||CHR(13), '\r\n', 1, 0)
INTO v_outtext
FROM dual;

以上写法转换提供的示例SQL，可完全得到预期输出：

SELECT SPRIDEN_ID, SPRIDEN_LAST_NAME, SPRIDEN_FIRST_NAME\r\nFROM sztdhof\r\nLEFT JOIN SPRIDEN\r\nON sztdhof.SZTDHOF_PIDM = spriden.SPRIDEN_PIDM\r\nRIGHT JOIN SSBSECT\r\nON sztdhof_CRN = SSBSECT_CRN \r\nWHERE spriden_change_ind IS NULL

反向还原（JSON回插数据库：字面量\r\n转CRLF） #

对应反向转换单语句写法：

SELECT REPLACE(v_json_content, '\r\n', CHR(13)||CHR(10))
INTO v_db_column
FROM dual;

之前尝试写法的错误点 #

• 写法1：仅替换了CHR(13)（回车符），每行末尾的CHR(10)（换行符）未被处理，因此替换后仍保留实际换行
• 写法2：仅替换了CHR(10)（换行符），残留的CHR(13)（回车符）未被处理，因此\r\n会出现在行首位置
• 写法3：语法错误，CHR(13)与CHR(10)之间缺少字符串拼接符||，正确写法为CHR(13)||CHR(10)
• 写法4：两处错误，一是Oracle正则不会将字符串中书写的\r\n识别为控制字符，仅作为普通字母处理；二是未传入全局替换参数，默认仅替换第一个匹配项

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