SQL生成XML疑难：多行数据空行替换位置异常求助

问题根源 #

你当前的SQL逻辑中，用于拆分文本的正则表达式'[^' || CHR(10) || ']+'会忽略连续换行之间的空内容（也就是空行）。因为这个正则只匹配「非换行符的字符序列」，空行位置没有符合条件的字符，所以regexp_substr返回null。但connect by计算的行数是包含空行的总行数，最终导致本该对应空行的位置没有触发else '.'，反而在最后一个行数位置才补全.。

修正后的SQL方案 #

方案1：使用支持捕获空行的正则表达式

通过非贪婪匹配的正则，捕获每一行的内容（包括空行），再判断内容是否为空来返回对应值：

select 
    level,
    case 
        when trim(regexp_substr(a.col_trimmed_value, '(.*?)(\'||CHR(10)||'|$)', 1, level, 'n', 1)) is not null
             and trim(regexp_substr(a.col_trimmed_value, '(.*?)(\'||CHR(10)||'|$)', 1, level, 'n', 1)) != ''
            THEN substr(regexp_substr(a.col_trimmed_value, '(.*?)(\'||CHR(10)||'|$)', 1, level, 'n', 1), 1, 200)
        ELSE '.'
    END as ROW_VALUE
from (
    select
        col1 as col_original_value,
        replace(rtrim(ltrim(replace(col1, CHR(10), '#s1p@2l3t#'), '#s1p@2l3t#'), '#s1p@2l3t#'), '#s1p@2l3t#', CHR(10)) as col_trimmed_value
    from table1
)a
connect by level <= length(a.col_trimmed_value) - length(replace(a.col_trimmed_value, CHR(10))) + 1
-- 防止多数据行时产生笛卡尔积
and prior a.col_original_value = a.col_original_value
and prior sys_guid() is not null;

方案2：通过字符串定位拆分（无需正则）

利用instr定位换行符位置，精准拆分每一行内容，包括空行：

select 
    level,
    case 
        when trim(substr(a.col_trimmed_value, 
                        case when level=1 then 1 else instr(a.col_trimmed_value, CHR(10), 1, level-1)+1 end,
                        case when instr(a.col_trimmed_value, CHR(10), 1, level) = 0 then length(a.col_trimmed_value)+1 
                             else instr(a.col_trimmed_value, CHR(10), 1, level) 
                        end - case when level=1 then 1 else instr(a.col_trimmed_value, CHR(10), 1, level-1)+1 end)) != ''
            THEN substr(substr(a.col_trimmed_value, 
                        case when level=1 then 1 else instr(a.col_trimmed_value, CHR(10), 1, level-1)+1 end,
                        case when instr(a.col_trimmed_value, CHR(10), 1, level) = 0 then length(a.col_trimmed_value)+1 
                             else instr(a.col_trimmed_value, CHR(10), 1, level) 
                        end - case when level=1 then 1 else instr(a.col_trimmed_value, CHR(10), 1, level-1)+1 end), 1, 200)
        ELSE '.'
    END as ROW_VALUE
from (
    select
        col1 as col_original_value,
        replace(rtrim(ltrim(replace(col1, CHR(10), '#s1p@2l3t#'), '#s1p@2l3t#'), '#s1p@2l3t#'), '#s1p@2l3t#', CHR(10)) as col_trimmed_value
    from table1
)a
connect by level <= length(a.col_trimmed_value) - length(replace(a.col_trimmed_value, CHR(10))) + 1
and prior a.col_original_value = a.col_original_value
and prior sys_guid() is not null;

效果验证 #

执行上述任一SQL后，将得到你预期的结果：

LEVEL   ROW_VALUE
==================
1   VALUE1
2   VALUE2
3   .
4   VALUE3
5   VALUE4

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