适配Oracle的正则表达式转换：处理CSV内嵌换行以识别行尾

我有一段CSV格式字符串，其中数据内嵌了\r\n（PL/SQL中对应chr(13)||chr(10)），需要忽略这些内嵌的回车换行，只定位并替换CSV实际行尾的\r\n为文本“CRLF”。

示例内容 #

目标CSV字符串 #

This is line 1,"abc\r\nxyz","and, comma, some more","abc\r\n123\r\nzyx",oh more text,"let's see","33,333.01"\r\nThis is line 2,"Hello, 321","Four\r\nand five...\r\nand six",and 123456\r\nThis is line 3

PL/SQL字符串定义 #

'This is line 1,"abc' || chr(13) || chr(10) || 'xyz","and, comma, some more","abc' || chr(13) || chr(10) || '123' || chr(13) || chr(10) || 'zyx",oh more text,"let''s see","33,333.01"' || chr(13) || chr(10) || 'This is line 2,"Hello, 321","Four' || chr(13) || chr(10) || 'and five...' || chr(13) || chr(10) || 'and six",and 123456' || chr(13) || chr(10) || 'This is line 3'

问题现状 #

我找到一个能处理该文本的正则表达式[\r\n](?![^\"]*(",))，可以将行尾的\r\n替换为“CRLF”，但直接放到Oracle的REGEXP_REPLACE中无法生效。我用v_TEXT变量存储上述字符串，执行的语句如下：

select
REGEXP_REPLACE( v_TEXT, '[' || chr(13) || chr(10) || '](?![^\"]*(",))', 'CRLF' )
from dual;

需求与预期输出 #

需要将该正则转换为Oracle兼容版本，实现仅替换CSV行尾的\r\n为“CRLF”，保留双引号内的内嵌换行。预期输出如下：

This is line 1,"abc\r\nxyz","and, comma, some more","abc\r\n123\r\nzyx",oh more text,"let's see","33,333.01"CRLFThis is line 2,"Hello, 321","Four\r\nand five...\r\nand six",and 123456CRLFThis is line 3

解决方案 #

Oracle的正则引擎不支持正向否定预查，这是原正则失效的核心原因。换用“匹配行尾CRLF时，确保前面双引号数量为偶数”的逻辑即可实现需求。

兼容Oracle的正则语句 #

SELECT REGEXP_REPLACE(
    v_TEXT,
    '(' || chr(13) || chr(10) || ')(?=(?:[^"]*"[^"]*")*[^"]*$)',
    'CRLF'
) FROM dual;

正则逻辑说明 #

• '(' || chr(13) || chr(10) || ')'：精准匹配目标CRLF换行符
• (?=(?:[^"]*"[^"]*")*[^"]*$)：正向预查验证，确保从当前CRLF到字符串末尾的双引号总数为偶数（说明该CRLF不在引号包裹的内容内）
  • (?:[^"]*"[^"]*")*：匹配0或多组“非引号内容+引号+非引号内容+引号”，每组对应一对闭合的双引号
  • [^"]*$：匹配剩余的非引号内容直到字符串结尾

执行该语句后，会准确替换CSV行尾的CRLF为“CRLF”，双引号内的内嵌换行将被完整保留，完全符合预期输出。

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