拆分字符串放入数组

2021-09-10 00:00:00 sql tsql sql-server string-split

我正在处理拆分字符串的 SQL Server 面部问题.我想实现一个将字符串拆分为数组的函数:

I am working on a SQL Server face problem which is splitting a string. I want to implement a function to split a string into an array:

Declare @SQL as varchar(4000)
Set @SQL='3454545,222,555'
Print @SQL

...我必须做的，所以我有一个数组:

total splitCounter=3
Arr(0)='3454545'
Arr(1)='222'
Arr(2)='555'

下面的split函数不能满足我上面的需要，把一个字符串拆分成一个数组.

Below split function doesn't satisfy my need above, splitting a string into an array.

CREATE FUNCTION [dbo].[SplitString]
(
    @String     varchar(max)
,   @Separator  varchar(10)
)
RETURNS TABLE
AS RETURN
(
    WITH
    Split AS (
        SELECT
            LEFT(@String, CHARINDEX(@Separator, @String, 0) - 1) AS StringPart
        ,   RIGHT(@String, LEN(@String) - CHARINDEX(@Separator, @String, 0)) AS RemainingString

        UNION ALL

        SELECT
            CASE
                WHEN CHARINDEX(@Separator, Split.RemainingString, 0) = 0 THEN Split.RemainingString
                ELSE LEFT(Split.RemainingString, CHARINDEX(@Separator, Split.RemainingString, 0) - 1)
            END AS StringPart
        ,   CASE
                WHEN CHARINDEX(@Separator, Split.RemainingString, 0) = 0 THEN ''
                ELSE RIGHT(Split.RemainingString, LEN(Split.RemainingString) - CHARINDEX(@Separator, Split.RemainingString, 0))
            END AS RemainingString
        FROM
            Split
        WHERE
            Split.RemainingString <> ''
    )

    SELECT
        StringPart
    FROM
        Split
)

如果您有任何疑问，请提前提问，谢谢.任何类型的建议都将被接受.

If you have any query please ask, thanks in advance. Any type of suggestion will be accepted.

推荐答案

Split function from 这里

Split function from Here

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

从函数中选择:

select *  FROM dbo.fnSplitString('3454545,222,555', ',')

退货

splitdata 
--------
3454545 
222
555

然后，如果您愿意，可以使用游标或 while 循环将每个人分配给一个变量.一个表本质上是一个数组.

Then using a cursor or a while loop assign each individual to a variable if you wish. A table is in-essence an array already.

相关文章