SQL Server - Split function in your database |
| Peter Bromberg replied to Burak Gunay at 19-Dec-06 04:21 |
 | will take the entire delimited string and return a table that you can program against:
CREATE FUNCTION [dbo].[fn_Split](@text varchar(8000), @delimiter varchar(20) = ' ') RETURNS @Strings TABLE ( position int IDENTITY PRIMARY KEY, value varchar(8000) ) AS BEGIN DECLARE @index int SET @index = -1 WHILE (LEN(@text) > 0) BEGIN SET @index = CHARINDEX(@delimiter , @text) IF (@index = 0) AND (LEN(@text) > 0) BEGIN INSERT INTO @Strings VALUES (@text) BREAK END IF (@index > 1) BEGIN INSERT INTO @Strings VALUES (LEFT(@text, @index - 1)) SET @text = RIGHT(@text, (LEN(@text) - @index)) END ELSE SET @text = RIGHT(@text, (LEN(@text) - @index)) END RETURN END
-- Example usage in a stored proc, to insert into Table variable: /* DECLARE @WORDS TABLE ( ID INT IDENTITY (1,1), string varchar(100) ) INSERT INTO @WORDS SELECT VALUE AS WORD FROM dbo.fn_Split( @WordsToInsert,' ') */ |
| Biography - Peter Bromberg |
 | Peter Bromberg is a C# MVP, MCP, and .NET expert who has worked in banking, financial and telephony for over 20 years. Pete focuses exclusively on the .NET Platform, and currently develops SOA and other .NET applications for a Fortune 500 clientele. Peter enjoys producing digital photo collage with Maya,playing jazz flute, the beach, and fine wines. You can view Peter's UnBlog and IttyUrl sites. Please post questions at forums, not via email! |  |
|
|