Split CSV String into Table in SQL Server

April 5, 2010

By default there is no function in SQL Server that can split comma separated string into Table (Rows). Following t-sql is custom made function that can split csv string into table.

CREATE FUNCTION dbo.SplitCSV (@CSVString VARCHAR(8000), @Delimiter CHAR(1))
RETURNS @temptable TABLE (items VARCHAR(8000))
AS
BEGIN
	DECLARE @pos INT;
	DECLARE @slice VARCHAR(8000);

	SELECT @pos = 1;
	IF LEN(@CSVString) < 1 OR @CSVString IS NULL RETURN;

	WHILE @pos!= 0
	BEGIN
		SET @pos = CHARINDEX(@Delimiter,@CSVString);
		IF @pos != 0
			SET @slice = LEFT(@CSVString, @pos - 1);
		ELSE
			SET @slice = @CSVString;

		IF( LEN(@slice) > 0)
			INSERT INTO @temptable(Items) VALUES (@slice);

		SET @CSVString = RIGHT(@CSVString, LEN(@CSVString) - @pos);
		IF LEN(@CSVString) = 0 BREAK;
	END
	RETURN
END

Usage:

SELECT * FROM dbo.SplitCSV ('Computer,Keyboard,Mouse,USB', ',');

Related Posts

• Generate series using T-SQL
• Data Mining in SQL Server 2008
• First Service Pack of SQL Server 2008
• Business Intelligence with SQL Server 2008
• Welcome to my dream World

Tags: SQL Server, T-SQL

Posted in Tips & Tricks