SqlServer Function（SplitText）

最新推荐文章于 2021-11-30 16:58:14 发布

haperkelu

最新推荐文章于 2021-11-30 16:58:14 发布

阅读量681

点赞数

分类专栏： SqlServer 文章标签： sqlserver function parameters table insert

本文链接：https://blog.csdn.net/haperkelu/article/details/5721068

版权

SqlServer 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

ALTER FUNCTION [dbo].[SplitTextIntForTest1]
(
    -- Add the parameters for the function here
    @text VARCHAR(5000),
    @delimiter CHAR(1)
)
RETURNS @Table TABLE ([Value] INT)
AS
BEGIN

    ;WITH csvtbl([INDEX],[NEXT])
    AS
    (
    SELECT [INDEX]=1, [NEXT]=CHARINDEX(@delimiter,@text + @delimiter)
    UNION ALL
    SELECT [INDEX]=[NEXT]+1, [NEXT] = CHARINDEX(@delimiter,@text + @delimiter,[NEXT] + 1)
    FROM csvtbl
    WHERE CHARINDEX(@delimiter,@text + @delimiter,[NEXT]+1) <> 0
    )
    INSERT INTO @Table([Value])
    SELECT SUBSTRING(@text,[INDEX],[NEXT]-[INDEX]) AS [Value]
    FROM csvtbl OPTION (MAXRECURSION 32767)

    RETURN
END

select * from dbo.SplitTextIntForTest('2,3,5,3,5,6,7')