Pages

Search Code Shode

Thursday, July 28, 2011

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!

اشتہارات