In this section we will create a user defined function for comma separated string to table using CTE in sql server.

Earlier we have used while loop to convert comma separated string to table in sql server.  You can review the post at “Comma separated string to table in sql” .

Here we are going to achieve the same functionality but in different way. We are going to use CTE(Common Table Expression) to convert comma separated string to table.  Below is the function which we can use to convert comma separated string to table. 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 NVARCHAR(2000),
	@DELIMITER CHAR(1)
)
RETURNS @TMPTABLE TABLE (COLUMNVALUE NVARCHAR(100))
AS
BEGIN
	;WITH CTE(START, [END]) AS
	(
		SELECT  1, CHARINDEX(@DELIMITER , @STRING )
		UNION ALL
		SELECT  [END] + 1, CHARINDEX(@DELIMITER, @STRING, [END] + 1)
		FROM CTE
		WHERE [END] > 0
	)
	INSERT INTO @TMPTABLE
	SELECT  SUBSTRING(@STRING , START, CASE WHEN [END] > 0 THEN [END] - START ELSE 9999 END) AS STRINGVALUE
	FROM CTE

	RETURN
END
GO

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 while loop or XML. Please follow below post for both of the option.

1. Comma separated string to table  using while loop in SQL
2. comma separated string to table using XML in sql server