SQL Server table valued function to return table of integers from a string

From time to time i need to generate a list of ids from a string of comma separated values. And this function comes courtsey of this post, which I have found to be the most straightforward way to do it.

The function below takes a string of comma separated integers and returns a table .

CREATE function IntegerCommaSplit(@ListofIds nvarchar(1000))
returns @rtn table (IntegerValue int)
AS
begin
While (Charindex(',',@ListofIds)>0)
Begin
Insert Into @Rtn
Select ltrim(rtrim(Substring(@ListofIds,1,Charindex(',',@ListofIds)-1)))
Set @ListofIds = Substring(@ListofIds,Charindex(',',@ListofIds)+len(','),len(@ListofIds))
end
Insert Into @Rtn
Select ltrim(rtrim(@ListofIds))return
end

As shown in the forum post you can use the returned table to join against or I have used it to loop round and perform inserts/updates with:

DECLARE @IntegerValue int
DECLARE cur CURSOR FOR
SELECT IntegerValue FROM dbo.IntegerCommaSplit('1,2,3,4,5')
OPEN cur
FETCH NEXT FROM cur INTO @IntegerValue

WHILE @@FETCH_STATUS = 0
BEGIN
--do something with @IntegerValue
FETCH NEXT FROM cur INTO @IntegerValue
END
CLOSE cur
DEALLOCATE cur

I know there are lots of other ways to do this out there but googling doesn’t seem to unearth many. Maybe I need a better SQL book…. This method will do for now though unless someone out there has a better solution which I’d like to hear as I’m still not that happy with this. I hazily remeber an XML solution and this may refer to it. (I’m going to bed now so its for later)