問題描述

我正在處理拆分字符串的 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.

這篇關于拆分字符串放入數組的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！