SQL按自定义分隔符拆分字符串按序生成多列的实现咨询

核心实现逻辑不依赖正则匹配、不做二次聚合，从根源解决特殊分隔符不兼容、拆分内容乱序的问题。

• 分隔符选择优先选用业务内容中绝对不会出现的低概率字符，比如示例使用的?，也可选择§、¤等符号，避开-、/、,这类文本中高频出现的符号，从源头避免分隔符和内容冲突
• 位置定位使用原生字符串查找函数，不依赖正则规则，兼容任意自定义分隔符
• 拆分时严格按照分隔符在字符串中的实际偏移量从左到右逐段截取，不做重排序处理，保证拆分后内容顺序和原始字符串完全一致

具体实现步骤 #

1. 计算拆分后总列数 #

先统计原字符串中分隔符的出现次数，拆分后的总列数 = 分隔符出现次数 + 1。
统计方式不需要复杂逻辑，直接通过长度差计算即可：
分隔符出现次数 = (原字符串总长度 - 替换掉所有分隔符后的字符串长度) / 单个分隔符长度
以示例输入Sales External?HR?Purchase Department、分隔符?为例，原字符串长度为32，移除所有?后长度为30，分隔符长度为1，可得分隔符共出现2次，最终需要输出3列，和预期一致。

2. 逐段按偏移量截取内容 #

从字符串左侧开始，逐个定位每一个分隔符的位置，按位置直接截取对应片段：

• 第1列：截取范围为字符串起始位置 到 第1个分隔符的前一位
• 中间列（第2到第N-1列）：截取范围为 上一个分隔符位置+分隔符长度 到 当前分隔符的前一位
• 最后1列：截取范围为 最后一个分隔符位置+分隔符长度 到 字符串末尾
  位置查找统一使用原生INSTR类字符串位置函数，这类函数是按字符精确匹配，不经过正则解析，不管分隔符是什么特殊字符，都能精准定位，不会出现REGEXP_INSTR不支持特殊字符的问题。

可直接复用的代码示例 #

以Oracle/大部分支持标准SQL的数仓语法为例，分隔符和源字符串都支持自定义：

WITH params AS (
    SELECT
        -- 替换为实际待拆分的源字符串
        'Sales External?HR?Purchase Department' AS src_str,
        -- 替换为实际使用的自定义分隔符
        '?' AS delimiter
    FROM dual
),
calc AS (
    SELECT
        src_str,
        delimiter,
        (LENGTH(src_str) - LENGTH(REPLACE(src_str, delimiter, ''))) / LENGTH(delimiter) AS delim_count
    FROM params
)
SELECT
    -- 第1列
    SUBSTR(src_str, 1, INSTR(src_str, delimiter, 1, 1) - 1) AS col_1,
    -- 第2列
    SUBSTR(
        src_str,
        INSTR(src_str, delimiter, 1, 1) + LENGTH(delimiter),
        INSTR(src_str, delimiter, 1, 2) - INSTR(src_str, delimiter, 1, 1) - LENGTH(delimiter)
    ) AS col_2,
    -- 第3列
    SUBSTR(src_str, INSTR(src_str, delimiter, 1, 2) + LENGTH(delimiter)) AS col_3
    -- 若分隔符数量更多，按照上述列的写法依次追加即可
FROM calc

运行上述代码后输出结果完全符合预期：

原有方案踩坑的对应解决说明 #

• 针对SPLIT_PART、LISTAGG方案顺序错乱问题：上述实现全程从左到右按分隔符出现顺序截取，不做分组、排序、聚合重排操作，拆分顺序和原始字符串完全一致，不会出现乱序
• 针对REGEXP_INSTR不支持特殊字符问题：使用原生INSTR做位置匹配，不依赖正则解析规则，任意字符作为分隔符都可以精准识别，不需要额外转义
• 针对分隔符和内容冲突问题：只要提前选用业务文本不会出现的特殊符号作为分隔符，就不会出现拆分错位的情况

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