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.

What do you mean with an "array"?, your function is a table valued function that should do what you want, there are no arrays in SQL Server — Lamak, Oct 28 '14 at 14:33
Lamak,thanks for reply.in sql server is it possible to get output Arr(0)='3454545' Arr(1)='222' Arr(2)='555' after split. — shamim, Oct 28 '14 at 14:36
I'm also not aware of arrays in sql server. However you can have a SplitString function return a table with two columns: position, and item. That way you can work with the items relative to their position in the original string. — hatchet - done with SOverflow, Oct 28 '14 at 14:41
hatchet thanks for your reply and valuable suggestion,will you please show me syntax on my above split function how to set position. — shamim, Oct 28 '14 at 14:52
RETURNS TABLE you need to return a TABLE. And don't return a count separate. Just count the TABLE. — paparazzo, Oct 28 '14 at 14:52
Blam,table row count not full fill my requirement,i need string split position also.please check my desired out put — shamim, Oct 28 '14 at 14:55
Are there any special or unusual requirements here? This is one of the most frequently asked questions on here so was any searching done before posting? There are MANY variations of doing a string split. If you have large input strings, SQL CLR has been shown to be the fastest. Else, use one of the XML methods (they are mostly the same and easy to find). Do NOT use anything with a WHILE loop or recursive CTE (as shown in the question). — Solomon Rutzky, Oct 28 '14 at 15:08
possible duplicate of [How do I split a string so I can access item x](http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x) — Solomon Rutzky, Oct 28 '14 at 15:35

score 3 · Answer 1 · answered Oct 28 '14 at 15:27

3

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.

answered Oct 28 '14 at 15:27

Hashman

151
5

Please stop adding "WHILE loop" and/or "Recursive CTE" split string functions to S.O. They are proven, many times over, to be horrible. Use either: SQLCLR, XML via inline TVF, or non-Recursive CTE with inline numbers table via inline TVF. Do _not_ use a multiline TVF. – Solomon Rutzky Oct 28 '14 at 15:34
Okay. I googled, and tested code that was the first response. You mentioned inline TLF and XML, and linked in another comment. Where would be the best place to look for the other two? – Hashman Oct 28 '14 at 17:43
Here is an answer I posted a while ago that has 3 options ( http://stackoverflow.com/questions/22163595/comma-separated-string-into-tables-column-in-sql-server/22164283#22164283 ) and here is a blog post from Aaron Bertrand ( http://sqlperformance.com/2012/07/t-sql-queries/split-strings ). – Solomon Rutzky Oct 28 '14 at 19:30

score 0 · Answer 2 · answered Oct 28 '14 at 15:18

0

--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

answered Oct 28 '14 at 15:18

shamim

6,640
20
85
151

This is better, but still not nearly as good as any of the 3 documented, proven means of splitting strings in SQL Server. This is using a multiline TVF which is _much_ slower than an inline TVF. – Solomon Rutzky Oct 28 '14 at 15:23
This is an example of an inline TVF using the XML method: http://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql/11973913#11973913 – Solomon Rutzky Oct 28 '14 at 15:51

Julio Rodriguez · Answer 3 · 2019-12-24T16:03:48.557

I have use some similar, but i put in into a table, so i have do like this way:

-----------------------------------6----3---4----3---3----4------------------------------------------
declare @cadena varchar(max) = '112331,211,1233,124,533,6341';      
declare @delimitador as char(1) = ',';  
declare @final as int = len(@cadena), @longitudMinima as int = len(reverse(substring(reverse(@cadena),0,charindex(@delimitador,reverse(@cadena)))));
declare @output as table(splits varchar(10));   

/*si no tiene delimitador no tiene sentido, es solo un elemento*/
if charindex(@delimitador,@cadena)>0        
    begin       
        print 'la cadena tiene' + cast(@final as varchar(10)) + ',deberá llegar a:' + cast(@longitudMinima as varchar(10));
        /*se manda hasta la longitud del ultimo elemento*/
        while @final<>@longitudMinima
        begin
            /*mientras se pueda parsear con substring*/
            if charindex(@delimitador,@cadena)>0
            begin
                /*se obtiene elemento de la cadena y la longitud desciende pues la cadena se reduce
                 *cuando se quita este elemento en el substring*/
                declare @element as varchar(100) = substring(@cadena,1,charindex(@delimitador,@cadena)-1);
                set @final = len(@cadena);
                set @cadena = substring(@cadena,charindex(@delimitador,@cadena)+1,@final);
                print 'agregando elemento:' + @element + '--cadena:' + @cadena + '--final:' + cast(@final as varchar(10));
            end
            else
            begin
                set @final = len(@cadena);
                print 'terminando en elemento:' + @cadena + '--cadena:' + @cadena + '--final:' + cast(@final as varchar(10));               
            end
        end     
    end
else 
    begin
        print 'elemento:' + @cadena + '--cadena:' + @cadena;
    end

to create the function i do this

create function [dbo].[returnTableFromString](
    @cadena as varchar(max),
    @delimitador as char(1)
)
returns @output table(splits varchar(100))
begin       
    declare @final as int = len(@cadena), @longitudMinima as int = len(reverse(substring(reverse(@cadena),0,charindex(@delimitador,reverse(@cadena)))));            
    /*si no tiene delimitador no tiene sentido, es solo un elemento*/
    if charindex(@delimitador,@cadena)>0        
        begin       
            --print 'la cadena tiene' + cast(@final as varchar(10)) + ',deberá llegar a:' + cast(@longitudMinima as varchar(10));
            /*se manda hasta la longitud del ultimo elemento*/
            while @final<>@longitudMinima
            begin
                /*mientras se pueda parsear con substring*/
                if charindex(@delimitador,@cadena)>0
                begin
                    /*se obtiene elemento de la cadena y la longitud desciende pues la cadena se reduce
                     *cuando se quita este elemento en el substring*/
                    declare @element as varchar(100) = substring(@cadena,1,charindex(@delimitador,@cadena)-1);
                    set @final = len(@cadena);
                    set @cadena = substring(@cadena,charindex(@delimitador,@cadena)+1,@final);
                    insert into @output values (@element)                   
                end
                else
                begin
                    set @final = len(@cadena);
                    insert into @output values (@cadena)                    
                end
            end     
        end
    else 
        begin
            insert into @output values (@cadena);
        end 
    return
end

Split string put into an array

3 Answers3

Linked