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
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',';|,~')