Split CSV String into Table in SQL Server

We all have some time bogged down into such situations where we require to update table from Comma/Character Separated Values.

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.

Here is the function that will return table when passed CSV:

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', ',');

Hope it helps!