0 Comments
  •   Posted in: 
  • SQL

I've seen a lot of people on the net looking for a SQL split user defined function. Looking around, there didn't seem to be anything solid and performant. I found this article from 4guysfromrolla, but looking at all those function calls makes me sick. So I wrote my own hopefully performant UDF. I removed the split character parameter, as almost all uses I saw were based on commas. But that should be very simple to add in.

Let me know if you think of any other ideas to make it faster or better.

SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO

CREATEFUNCTIONSplit
(
@listvarchar(255)
)
RETURNS@listTableTABLE(valint)
AS
BEGIN
DECLARE@lenint
DECLARE@startPosint
DECLARE@endPosint

SET@len=LEN(@list)
SET@startPos=1
SET@endPos=CHARINDEX(',',@list)

WHILE(@endPos>0)
BEGIN
INSERTINTO@listTable
SELECTSUBSTRING(@list,@startPos,@endPos-@startPos)

SET@startPos=@endPos+1
SET@endPos=CHARINDEX(',',@list,@startPos)
END

IF@startPos<=@len
INSERTINTO@listTable
SELECTSUBSTRING(@list,@startPos,(@len+1)-@startPos)

ENDRETURN

GO