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 is a C# MVP, MCP, and .NET expert who has worked in banking ,financial and telephony for 20 years. Pete focuses exclusively on the .NET Platform, and his samples at GotDotNet.com have been downloaded over 56,000 times. Peter enjoys producing 3D raytraced digital photo collage with Maya, the beach, and fine wines. You can view Peter's UnBlog and IttyUrl sites. Please post questions at forums, not via email! |  |  |
|