create FUNCTION [dnt_split] ( @splitstring NVARCHAR(4000), @separator CHAR(1) = ‘,‘ ) RETURNS @splitstringstable TABLE ( [item] NVARCHAR(200) ) AS BEGIN DECLARE @currentindex INT DECLARE @nextindex INT DECLARE @returntext NVARCHAR(200) SELECT @currentindex=1 WHILE(@currentindex<=datalength(@splitstring)/2) BEGIN SELECT @nextindex=charindex(@separator,@splitstring,@currentindex) IF(@nextindex=0 OR @nextindex IS NULL) SELECT @nextindex=datalength(@splitstring)/2+1 SELECT @returntext=substring(@splitstring,@currentindex,@nextindex-@currentindex) INSERT INTO @splitstringstable([item]) VALUES(@returntext) SELECT @currentindex=@nextindex+1 END RETURN END
上面版本，最多支持4000个字符，客户说选太多项目时会丢失数据，分析函数，直接把nvarchar(4000)改成Text，可以支持更长的字符串，自信满满的在客户机子上运行，居然有异常，跟踪代码如果只输入
1216，‘，’,返回结果是12，艹，输入‘1216,’,‘,‘显示正常，不应该啊，仔细看代码有/2部分，猜测可能是nvarchar计算长度问题，去掉除以/2,如下所示，正常。
ALTER FUNCTION [dbo].[dnt_split] ( @splitstring TEXT, @separator CHAR(1) = ‘,‘ ) RETURNS @splitstringstable TABLE ( [item] VARCHAR(200) ) AS BEGIN DECLARE @currentindex INT DECLARE @nextindex INT DECLARE @returntext VARCHAR(200) SELECT @currentindex=1 WHILE(@currentindex<=datalength(@splitstring)) BEGIN SELECT @nextindex=charindex(@separator,@splitstring,@currentindex) IF(@nextindex=0 OR @nextindex IS NULL) SELECT @nextindex=datalength(@splitstring)+1 SELECT @returntext=substring(@splitstring,@currentindex,@nextindex-@currentindex) INSERT INTO @splitstringstable([item]) VALUES(@returntext) SELECT @currentindex=@nextindex+1 END RETURN END
原文：https://www.cnblogs.com/zhaogaojian/p/10027906.html