The table datatype in SQL Server 2000 and higher is really useful and allow for nice constructs. The example I am gonna show you here is CSV parsing - and the code is developed by Erland Sommerskog - all I have done is taken away the row number parameter in the resultset (which I typically don't need) and changed the delimiter character from ' ' (space) to ',' (comma).
create FUNCTION ParseIntCSV (@list ntext)
RETURNS @tbl TABLE ( id int NOT NULL) AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@str nvarchar(4000),
@tmpstr nvarchar(4000),
@leftover nvarchar(4000)
SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))
SET @textpos = @textpos + @chunklen
SET @pos = charindex(',', @tmpstr)
WHILE @pos > 0
BEGIN
SET @str = substring(@tmpstr, 1, @pos - 1)
INSERT @tbl (id) VALUES(convert(int, @str))
SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
SET @pos = charindex(',', @tmpstr)
END
SET @leftover = @tmpstr
END
IF ltrim(rtrim(@leftover)) <> ''
INSERT @tbl (id) VALUES(convert(int, @leftover))
RETURN
END
We can now use it like this
select * from ParseIntCSV('12,39, 122')
which gives us a resultset of ints:
And that allows us to do even more useful things - like joining on the resulting set:
declare @playerids as varchar(25)
select @playerids='1176,1182, 1184'
select * from player, ParseIntCSV(@playerids) tab
where player.id=tab.id
In real life, the @playerids variable is probably a parameter to a stored procedure but you get the point, I hope.
