SQL 2005 - Easiest Way To Convert CSV To Table

Here is a function that will easily let you convert from a CSV (you can modify it to be ;sv or -sv... you get the idea!) to a Table using SQL's Table-valued Functions.

SQL Function:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE FUNCTION [dbo].[charlist_to_table]
(@list ntext,
@delimiter char(1) = N',')
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL, str varchar(4000), nstr varchar(2000)) AS
BEGIN

DECLARE @pos int, @textpos int, @chunklen smallint, @tmpstr varchar(4000), @leftover varchar(4000), @tmpval varchar(4000)
SET @textpos = 1 SET @leftover = ''

WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2 SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen) SET @textpos = @textpos + @chunklen SET @pos = charindex(@delimiter, @tmpstr)

WHILE @pos > 0
BEGIN
SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) SET @pos = charindex(@delimiter, @tmpstr)
END

SET @leftover = @tmpstr
END

INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover))) DELETE FROM @tbl WHERE LTRIM(ISNULL(str,'')) = ''

RETURN
END

USAGE:
SELECT * from dbo.charlist_to_table('ankeet, megha, kamlesh, ragini', Default)

This is not something out of the ordinary, but I thought I should post it out here for others like me who do not like to re-invent the wheel!



Please consider the environment before printing this blog! Go Green!