Split string put into an array

Question

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

…what I have to do so I have an array in which I have:

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

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.

Answer 1

--input

SELECT   * FROM     SplitStringShamim('A,B,C,DDDDDD,EEE,FF,AAAAAAA', ',')


create FUNCTION [dbo].[SplitStringShamim]
(
    @String     varchar(max)
,   @Separator  varchar(10)
)

RETURNS @DataSource TABLE
(
    [ID] TINYINT IDENTITY(1,1)
   ,[Value] NVARCHAR(128)
)   
AS


begin 
            DECLARE @Value NVARCHAR(MAX) = @String

            DECLARE @XML xml = N'<r><![CDATA[' + REPLACE(@Value, @Separator, ']]></r><r><![CDATA[') + ']]></r>'

            INSERT INTO @DataSource ([Value])
                    SELECT RTRIM(LTRIM(T.c.value('.', 'NVARCHAR(128)')))
                    FROM @xml.nodes('//r') T(c)

        return 

end   

Answer 2

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

Selecting from the function:

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

Returns

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

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.