SQL 2012及以上、Oracle 10g环境下实现保序字符串分割的方案

核心实现逻辑 #

以往的字符串分割方案大多直接返回查询结果，没有绑定明确的排序依据；当结果集写入临时表时，数据库的临时存储页会按照自身的分配规则写入，和原字符串的元素顺序无关，最终读取时就会出现顺序错乱。
本次方案从根源解决该问题：在分割过程中同步记录每个元素在原字符串中的绝对位置索引，最终按索引升序输出结果，完全不依赖数据库临时表的默认返回顺序，可同时适配SQL Server 2012及以上、Oracle 10g的版本要求。 #

SQL Server 2012 及以上版本实现 #

利用XML节点拆解的方式实现分割，全程不依赖高版本专属的STRING_SPLIT函数，兼容2012版本的能力边界：

-- 示例：分割逗号分隔的字符串 'a,b,c,d,e'
DECLARE @inputStr VARCHAR(100) = 'a,b,c,d,e';
DECLARE @splitChar CHAR(1) = ',';

;WITH split_base AS (
    -- 将原字符串转换为XML可拆解格式，把分隔符替换为独立XML节点
    SELECT CAST('<x>' + REPLACE(@inputStr, @splitChar, '</x><x>') + '</x>' AS XML) AS str_xml
),
split_with_order AS (
    SELECT 
        n.value('.', 'VARCHAR(100)') AS split_value,
        -- 基于XML节点的原生生成顺序，标记每个元素的原始位置
        ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS element_order
    FROM split_base
    CROSS APPLY str_xml.nodes('/x') AS t(n)
)
-- 最终按元素位置索引排序，结果顺序与原串完全一致
SELECT split_value, element_order
FROM split_with_order
ORDER BY element_order;

实现注意点：

• XML拆解时，节点的生成顺序和原字符串的元素顺序完全绑定，不会受执行计划变化影响
• 最终结果强制按element_order排序，哪怕临时表的物理存储顺序打乱，最终输出顺序也不会错乱

Oracle 10g 版本实现 #

利用正则匹配配合递归遍历的方式实现分割，不依赖11g及以上才支持的递归CTE、正则增强等特性：

-- 示例：分割逗号分隔的字符串 'a,b,c,d,e'
WITH split_param AS (
    SELECT 
        'a,b,c,d,e' AS input_str,
        ',' AS split_char,
        -- 计算分割后的总元素个数，作为递归遍历的终止条件
        LENGTH('a,b,c,d,e') - LENGTH(REPLACE('a,b,c,d,e', ',', '')) + 1 AS total_element_cnt
    ),
split_calc AS (
    SELECT 
        -- 提取对应位置的分割元素
        REGEXP_SUBSTR(input_str, '[^'||split_char||']+', 1, LEVEL) AS split_value,
        -- 显式计算每个元素在原串中的绝对起始位置，作为排序依据
        CASE 
            WHEN LEVEL = 1 THEN 1 -- 第一个元素的起始位置为1
            ELSE INSTR(input_str, split_char, 1, LEVEL - 1) + 1 
        END AS element_pos
    FROM split_param
    CONNECT BY 
        LEVEL <= total_element_cnt
        AND PRIOR input_str = input_str
        -- 10g兼容的递归终止判断，避免触发ORA-30009递归错误
        AND PRIOR DBMS_RANDOM.VALUE() IS NOT NULL
)
-- 最终按元素在原串的实际位置排序，结果顺序与原串完全一致
SELECT split_value, element_pos
FROM split_calc
WHERE split_value IS NOT NULL
ORDER BY element_pos;

实现注意点：

• 计算元素位置时针对首元素做了边界处理，避免INSTR函数传入无效参数报错
• 排序依据是元素在原串中的实际字符位置，完全不依赖CONNECT BY的遍历返回顺序，哪怕遍历过程因为执行计划变化打乱顺序，最终排序结果也会和原串完全匹配

通用适配注意事项 #

• 如果分割元素本身包含特殊字符、空值，需要根据实际业务场景调整XML转义规则、正则匹配逻辑，避免分割结果错误
• 跨版本兼容场景下，不建议直接使用数据库内置的分割函数（如SQL Server的STRING_SPLIT、Oracle的内置分割包），这类函数的行为在不同版本中差异较大，容易出现兼容问题

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