循环拆分实现
CREATE FUNCTION [dbo].[SplitString]
(
@str NVARCHAR(4000)
,@char NVARCHAR(10) = ','
)
RETURNS @SplitStr TABLE
(
ID int IDENTITY PRIMARY KEY
,Value nvarchar(2000)
)
AS
BEGIN
SET @str = @str + @char
WHILE LEN(@str) > 0
BEGIN
INSERT @SplitStr
SELECT SUBSTRING(@str, 1, CHARINDEX(@char, @str) - 1)
SELECT @str = RIGHT(@str, LEN(@str) - CHARINDEX(@char, @str) - (LEN(@char) - 1))
END
RETURN
END
以上方法从SQL SERVER 2000时代就可以使用，可以说是最具版本兼容性的写法。
递归函数实现
CREATE FUNCTION [dbo].[SplitString_CTE]
(
@String NVARCHAR(4000),
@Delimiter NVARCHAR(10)
)
RETURNS TABLE
AS
RETURN
(
WITH Split(StartPos,EndPos) AS
(
SELECT 0 AS StartPos, CHARINDEX(@Delimiter,@String) AS EndPos
UNION ALL
SELECT EndPos+LEN(@Delimiter), CHARINDEX(@Delimiter,@String,EndPos+LEN(@Delimiter))
FROM Split
WHERE EndPos > 0
)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID
,SUBSTRING(@String,StartPos,COALESCE(NULLIF(EndPos,0),LEN(@String)+1)-StartPos) AS Value
FROM Split
--OPTION(MAXRECURSION 0) 这里不能使用，要放在最终查询中使用
)
从SQL SERVER 2005 开始，出现了公共表达式这一利器，可以实现递归查询，于有了上面的版本。
基本思路是从上一次查询分隔符的位置加1开始成为新的起点，一直递归到找不到分隔符为止。
CTE的结果是每个分隔符号的起始位置，最外层查询是利用起始位置查询出最终结果。
XML实现
CREATE FUNCTION [dbo].[SplitString_XML]
(
@String NVARCHAR(4000),
@Delimiter NVARCHAR(10)
)
RETURNS TABLE
AS
RETURN
(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS ID,b.Value
FROM
(
SELECT CAST(('<V>'+REPLACE(@String,@Delimiter,'</V><V>')+'</V>') AS XML) AS XMLString
) AS a
CROSS APPLY
(
SELECT N.value('.', 'nvarchar(10)') AS Value
FROM a.XMLString.nodes('V') As T(N)
) AS b
)
此方法通过构造XML字符串去使用XML的特有方法进行实现。
结果展示
SELECT * FROM [dbo].[SplitString]('12,123,21312',',')
SELECT * FROM [dbo].[SplitString_CTE]('12,123,21312',',')
SELECT * FROM [dbo].[SplitString_XML]('12,123,21312',',')
均得到了正确的结果：
image.png
性能提示
通过执行计划评估以及时间统计，发现CTE版本的性能最好。但如果分隔的项目太多，可能会超过SQL Server默认的最大100层递归，需要在查询后面加上OPTION(MAXRECURSION 0)以防止报错。
常用的循环法性能次之，主要消耗在每次的表变量插入上。
XML的性能最差，不建议使用。
分列
有了以上对单个字符串进行拆分的函数，可以利用它们进行字符串分列（根据分隔符将字符串拆分为多列）。
如下，左边的列表，想要变为右边的列表。
image.png
选择以上函数中的一种实现，代码如下：
-- 生成测试数据
CREATE TABLE Test_TB(Title NVARCHAR(200))
INSERT Test_TB VALUES ('AA-BB-CC'),('CC-DD-EE'),('XX-YY'),('YY-ZZ')
-- 分列转换
SELECT Title,[1],[2],[3]
FROM (SELECT a.Title,b.ID,b.Value
FROM Test_TB a
CROSS APPLY [dbo].[SplitString](a.Title,'-') AS b
) AS a
PIVOT(MAX(Value) FOR ID IN ([1],[2],[3])) AS b
结果如下：
image.png
后话
最后说一点题外话，SQL Server 2016对字符串的拆分和合并已经有了内置的系统函数STRING_SPLIT和STRING_AGG。以后再也没有自己实现的乐趣了，感慨编程语言越来越强大，轮子越来越多，多年以后可能傻瓜都会编程了，编程将和英语一样成为一门多数人都要会的通用技能。
