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