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.
- comma separated string to table using CTE in sql server
- comma separated string to table using XML in sql server