sqlserver split函数

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,@currentindex)
        IF(@nextindex=0 OR @nextindex IS NULL)
            SELECT @nextindex=datalength(@splitstring)+1
        
        SELECT @returntext=substring(@splitstring,@nextindex-@currentindex)

        INSERT INTO @splitstringstable([item])
        VALUES(@returntext)
        
        SELECT @currentindex=@nextindex+1
    END
    RETURN
END

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!