Technical Article

Split string using multiple delimiters - version 2

,

In my previous script, it will split the string upto 256 length.

http://www.sqlservercentral.com/scripts/split+string/117123/

This new version will split the string upto "N" length.

Regards,

Vignesh Arulmani

CREATE FUNCTION dbo.fn_generate_numbers_v2
(@NumRows INT)
RETURNS @returnTable TABLE (RowNum INT PRIMARY KEY)
AS
BEGIN

DECLARE @idt INT
SET @idt = 0
WHILE (@idt < @NumRows)
BEGIN
SELECT @idt = @idt + 1
INSERT INTO @returnTable
SELECT @idt
END
RETURN
END
GO


CREATE FUNCTION dbo.[fn_split_string_using_multiple_delimiters_v2]
(
      @String    VARCHAR(MAX),  -- input string
      @delimiter VARCHAR(32)    -- delimiter list 
)
RETURNS @Table TABLE(rowid INT IDENTITY PRIMARY KEY,        
items VARCHAR(MAX)
)
BEGIN
 
        DECLARE @Xml AS XML
        DECLARE @derived_string VARCHAR(MAX)
 
        ;WITH N1 (n) AS (
SELECT RowNum AS [n] FROM dbo.fn_generate_numbers_v2(len(@String))
)

        SELECT @derived_string=STUFF((SELECT '' + (Case When
                PATINDEX('%[' + @delimiter + ']%',SUBSTRING(@String,Nums.n,1)) >0
                Then ',' else LTRIM(RTRIM(SUBSTRING(@String,Nums.n,1))) end)
        FROM N1 Nums WHERE Nums.n<=LEN(@String)  FOR XML PATH('')),1,0,'')
 
        SET @Xml = cast(('<a>'+replace(@derived_string,
                ',','</a><a>')+'</a>') AS XML)
 
        INSERT INTO @Table SELECT DISTINCT REPLACE(A.value('.', 'VARCHAR(MAX)'),CHAR(13)+CHAR(10),'')
                as [Column] FROM @Xml.nodes('a') AS FN(a)
 
RETURN
END
GO


select * from [fn_split_string_using_multiple_delimiters_v2](
'http://www.google.com;
http://www.yahoo.com|
http://www.msn.com~
http://www.twitter.com,
http://www.facebook.com~
http://www.sqlservercentral.com;
http://www.social.technet.microsoft.com,
http://www.sqlmag.com;
http://www.sqlperformance.com,
http://www.sqlteam.com',';|,~')

Rate

5 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (3)

You rated this post out of 5. Change rating