SQL UDF用SUBSTRING搭配UNION拆分字符串顺序异常问题求解

问题根源 #

1. 插入数据时使用了UNION拼接多个查询结果，UNION会自动对结果集执行去重+升序排序操作，直接打乱了子串原本的先后顺序。
2. 调用UDF查询时没有显式添加ORDER BY seq语句，SQL本身不保证无排序规则时的返回顺序，即使seq是自增列，不加排序也可能出现乱序。
3. 原有实现中拆分的起始位置计算错误，每7个字符拆分的话，起始位应为1、8、15、22、29、36、43，原有写的23、30、37、44会漏掉一位字符。

修正方案 #

1. 修正UDF定义

把UNION全部替换为UNION ALL，UNION ALL不会做额外的排序和去重，会保留SELECT语句的先后顺序，同时修正拆分起始位：

CREATE OR ALTER FUNCTION dbo.UDF_SplitStringIntoRows
(
    @inputstring nvarchar(MAX)
)
RETURNS @OutputTbl TABLE
(
    txt nvarchar(MAX),
    seq INT IDENTITY 
)
AS
BEGIN
    IF(LEN(@inputstring) <= 40)
    BEGIN 
        INSERT INTO @OutputTbl (txt) 
            SELECT SUBSTRING(@inputstring, 1,7) UNION ALL
            SELECT SUBSTRING(@inputstring, 8,7) UNION ALL
            SELECT SUBSTRING(@inputstring, 15,7) UNION ALL
            SELECT SUBSTRING(@inputstring, 22,7) UNION ALL
            SELECT SUBSTRING(@inputstring, 29,7) UNION ALL
            SELECT SUBSTRING(@inputstring, 36,7) UNION ALL
            SELECT SUBSTRING(@inputstring, 43,7)
    END

    RETURN
END

2. 修正查询语句

调用时显式加ORDER BY seq保证返回顺序和拆分顺序一致：

SELECT *
FROM dbo.UDF_SplitStringIntoRows('This is a demo function which') AS USSIR
WHERE USSIR.txt <> ''
ORDER BY seq

扩展：支持任意长度字符串的优化实现 #

原有实现仅支持长度<=40的输入，可使用递归CTE改写为内联表值函数，性能更好且适配任意长度输入：

CREATE OR ALTER FUNCTION dbo.UDF_SplitStringIntoRows
(
    @inputstring nvarchar(MAX)
)
RETURNS TABLE
AS
RETURN
(
    WITH cte AS
    (
        SELECT 1 AS seq, SUBSTRING(@inputstring, 1, 7) AS txt
        UNION ALL
        SELECT seq + 1, SUBSTRING(@inputstring, seq * 7 + 1, 7)
        FROM cte
        WHERE seq * 7 + 1 <= LEN(@inputstring)
    )
    SELECT txt, seq FROM cte
)

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