Split CSV String into Table in SQL Server

April 5, 2010 by 4 Comments

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', ',');
Filed Under: Tips & Tricks Tagged With: ,

Generate series using T-SQL

February 26, 2010 by 2 Comments

It is common developer requirement to get missing records from series. For that they generate temporary table and insert complete list of numbers and then find missing numbers using LEFT JOIN. In SQL Server 2005 and 2008, new keyword “WITH” introduced that works with SELECT, INSERT and UPDATE statement that use to create temporary resultset using Common Table Expression (CTE).

Example 1:


      BEGIN
            WITH mycte AS
            (
                  SELECT 1 id
                  UNION ALL
                  SELECT id + 1
                  FROM    mycte  
                  WHERE  id + 1 < = 100
            )
            SELECT id
            FROM mycte
            OPTION (MAXRECURSION 0)
        End

Example 2:
A company awarded performance award to their employee each year. Requirement is to find out the list of year in which performance award did not given to any employee.


      BEGIN
            Create Table Performance (EmployeeID int, [Year] int);
            Insert Into Performance VALUES (100, 2001);
            Insert Into Performance VALUES (200, 2003);
            Insert Into Performance VALUES (150, 2007);
            Insert Into Performance VALUES (155, 2008);

            WITH mycte AS
            (
                  -- suppose 2001 is the first year of the company
                  SELECT 2001 As AwardedYear
                  UNION ALL
                  SELECT AwardedYear + 1
                  FROM    mycte
                  WHERE  AwardedYear + 1 < = Year(GetDate()) - 1
            )
            SELECT t.AwardedYear
            FROM mycte t LEFT JOIN Performance P ON (t.AwardedYear = P.Year)
            WHERE P.Year IS NULL
            OPTION (MAXRECURSION 0)
            -- MAXRECURSION 0 means there is no limit of query recursion
        End
Filed Under: Microsoft, Programming Tagged With: ,