Another one too.
CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
-- ERO FIRST TIME IN LOOP
SELECT @INDEX = 1
WHILE @INDEX !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRI
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(Items) VALUES(@SLICE)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
END
Main Topics
Browse All Topics
by: sachiekPosted on 2003-10-03 at 00:13:35ID: 9482920
Here is one such function.
CREATE FUNCTION dbo.fSplit
(@Text varchar(200), @Delimiter char(1))
RETURNS @Table table(Code int, strName varchar(50))
AS
BEGIN
DECLARE @I AS integer
DECLARE @Start AS integer
DECLARE @End AS integer
DECLARE @C AS char(1)
DECLARE @Count AS integer
SET @I = 1
SET @Start = 1
SET @End = 0
SET @Count = 1
WHILE @I < len(@Text)
BEGIN
SET @C = substring(@Text, @I, 1)
IF @C = @Delimiter
BEGIN
SET @End = @I
END
IF @End > 0
BEGIN
INSERT INTO @Table values(@Count, substring(@Text, @Start, @I - @Start))
SET @Start = @I + 1
SET @End = 0
SET @Count = @Count + 1
END
SET @I = @I + 1
END
INSERT INTO @Table values(@Count, substring(@Text, @Start, @I - @Start + 1))
RETURN
END
..
Sachi