Wana make your own split function for SQL SERVER ?

Question

Wana make your own split function for SQL SERVER ?

womalley

Ten Centuries

Points: 1329
More actions
October 22, 2003 at 12:34 pm

#82453

Try this,
I had to write a split function so here is the code
------------------------------------------------
DECLARE @IDs VARCHAR(200)
DECLARE @CounterINT
DECLARE @StrLenINT
DECLARE @NewIDStringVARCHAR(200)
DECLARE @CharToSplitOnVARCHAR(2)
DECLARE @TempTable TABLE
(IDS INT NOT NULL DEFAULT 0)
SET @IDs = '1^2^3^4^5'
SET @Counter = 0
SET @CharToSplitOn = '^'
SET @StrLen = LEN(REPLACE(@IDs,LTRIM(RTRIM(@CharToSplitOn)),'') )
WHILE @Counter < @StrLen-1 BEGIN
IF (@Counter = 0) BEGIN
-- get the first value in the strin
SET @NewIDString = SUBSTRING(@IDs,0,PATINDEX('%' + LTRIM(RTRIM(@CharToSplitOn)) + '%',@IDs))
-- insert that value into the table
INSERT INTO @TempTable
(IDS)
VALUES
(CAST(REPLACE(@NewIDString,@CharToSplitOn,'') AS INT))
END
-- get the next number in the series
SET @NewIDString = SUBSTRING(@IDs,PATINDEX('%' + LTRIM(RTRIM(@CharToSplitOn)) + '%',@IDs)+1,PATINDEX('%' + LTRIM(RTRIM(@CharToSplitOn)) + '%',@IDs))
-- set the ID string to the new string
SET @IDs = SUBSTRING(@IDs,PATINDEX('%' + LTRIM(RTRIM(@CharToSplitOn)) + '%',@IDs)+1,LEN(@IDs) )
-- insert the number
INSERT INTO @TempTable
(IDS)
VALUES
(CAST(REPLACE(@NewIDString,LTRIM(RTRIM(@CharToSplitOn)),'') AS INT))
-- increment the counter and do again.
SET @Counter = @Counter + 1
END
SELECT * FROM @TempTable
-----------------------------------------
enjoy
Will

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

That has a problem if your list contains just one entry. How about something like this:



CREATE FUNCTION dbo.f_SplitInts(@List varchar(8000), @Sep char)

RETURNS @t TABLE(Piece int) BEGIN

DECLARE @pos smallint

SET @List = LTRIM(RTRIM(@List)) + @Sep

SET @pos = CHARINDEX(@Sep, @List)

WHILE @pos > 1 BEGIN

 INSERT @t VALUES(left(@List, @pos-1))

 SET @List = STUFF(@List, 1, @pos,'')

 SET @pos = CHARINDEX(@Sep, @List)

END

RETURN END

--Jonathan

womalley Ten Centuries Points: 1329 More actions · Answer 2

Yeah I ended having to put a test in there that I forgot to check and see how many entries there were.

Thanks for the feed back

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 3

Jonathan, nice solution!

I just removed the STUFF part.

CREATE FUNCTION f_SplitInts(@List Varchar(8000), @Sep Char)

    RETURNS @t TABLE(Piece int) 

    BEGIN

        DECLARE @pos smallint

        DECLARE @Start SmallInt

        SET @List = LTRIM(RTRIM(@List)) + @Sep

        SET @pos = CHARINDEX(@Sep, @List,1)

        Set @Start=1

        WHILE @pos > 1 

            BEGIN

                If (@Pos>@Start) -- Ignore empty delimited strings

                    INSERT @t VALUES(SubString(@List, @Start,@pos-@Start))

                SET @Start=@Pos+1

                SET @pos = CHARINDEX(@Sep, @List,@Start)

    END

    RETURN 

END

GO

Select * from dbo.f_SplitInts('1a2a33a44a55a451a1a1aa1','a')

Select * from dbo.f_SplitInts('aaa','a')

Select * from dbo.f_SplitInts('','a')

go

Drop Function dbo.f_SplitInts

GO

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

... http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Adam Machanic SSCoach Points: 15370 More actions · Answer 5

http://www.algonet.se/~sommar/arrays-in-sql.html

Excellent article.

--
Adam Machanic
whoisactive