Oracle 19c使用regexp_substr按换行拆分字段并过滤空值的方法

问题根因 #

• 原查询正则匹配逻辑错误：使用的'['||chr(10)||']'规则仅匹配换行符本身，无法提取换行符分隔的有效文本内容，因此无有效结果返回
• 缺少内容清洗逻辑：未处理行首的-、空格等冗余前缀
• 层级查询逻辑不完善：未添加行唯一标识限制，多数据场景下会生成笛卡尔积导致结果异常
• 缺少空值过滤规则：未剔除拆分后产生的空行内容

正确实现代码 #

SELECT 
    ft.field_id,
    -- 清洗行首冗余字符+清除首尾空白
    TRIM(REGEXP_REPLACE(
        REGEXP_SUBSTR(ft.validation_data, '[^'||CHR(10)||']+', 1, LEVEL),
        '^[-[:space:]]+', ''
    )) AS str
FROM mytable ft
WHERE ft.validation_data IS NOT NULL
CONNECT BY 
    -- 拆分行数不超过分隔后的总段数
    LEVEL <= REGEXP_COUNT(ft.validation_data, '[^'||CHR(10)||']+')
    -- 避免多数据行交叉生成错误结果
    AND PRIOR ft.ROWID = ft.ROWID
    AND PRIOR SYS_GUID() IS NOT NULL
-- 过滤清洗后的空值结果
HAVING TRIM(REGEXP_REPLACE(
    REGEXP_SUBSTR(ft.validation_data, '[^'||CHR(10)||']+', 1, LEVEL),
    '^[-[:space:]]+', ''
)) IS NOT NULL;

适配说明 #

如果字段中换行符是Windows格式（CHR(13)||CHR(10)），可将匹配规则中的'[^'||CHR(10)||']+'替换为'[^'||CHR(13)||CHR(10)||']+'，即可兼容两种换行格式。

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