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.

SET

ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE

 FUNCTION Split
(
   @list varchar(255)
)
RETURNS @listTable TABLE (val int)
AS
BEGIN
   DECLARE @len int
   DECLARE @startPos int
   DECLARE @endPos int

 

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

 

   WHILE (@endPos > 0)
   BEGIN
      INSERT INTO @listTable
         SELECT SUBSTRING(@list, @startPos, @endPos - @startPos)

 

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

 

   IF @startPos <= @len
      INSERT INTO @listTable
         SELECT SUBSTRING(@list, @startPos, (@len + 1) - @startPos)

 

END

   RETURN

GO