There are lot many option a programmer can have to achieve Get comma separated string to table in sql server in different way. To convert comma separated string to table we are gonna use a simple table valued function to get our desired output.

Below is the table-valued function to convert comma separated string  to table. Please note that in below function it is not compulsory to always use comma. A string can have any type of separator.

CREATE FUNCTION [dbo].[Split](@String VARCHAR(MAX), @Delimiter CHAR(1))
RETURNS @temptable TABLE (ColValue VARCHAR(200))
AS
BEGIN
    DECLARE @Idx INT
    DECLARE @SubStr VARCHAR(MAX)

	SELECT @idx = 1
    IF LEN(@String)<1 OR @String IS NULL  RETURN

    WHILE @idx!= 0
    BEGIN
        SET @idx = CHARINDEX(@Delimiter,@String)
        IF @idx!=0
            SET @SubStr = LEFT(@String,@idx - 1)
        ELSE
            SET @SubStr = @String

        IF(LEN(@SubStr)>0)
            INSERT INTO @temptable(ColValue) VALUES(@SubStr)

        SET @String = RIGHT(@String,LEN(@String) - @idx)
        IF LEN(@String) = 0 BREAK
    END
RETURN
END

Now to test above function execute below query.

SELECT * FROM DBO.[SPLIT]( ‘ONE,TWO,THREE,FOUR,FIVE,SIX,SEVEN,EIGHT,NINE,TEN’,’,’)

The output of above query is something like below screen shot.

 

We can also achieve the same functionality with the help of CTE or XML. Please follow below post for both of the option.

1. comma separated string to table using CTE in sql server
2. comma separated string to table using XML in sql server