T-SQL : Comma separated values to table

In this post, I'm trying to explain you how one parse the comma separated string to a table so that which can be used as any other regular table.

This would work only SQL Server 2005 Onwards releases as I'm creating table valued function to accomplish this.

I have created a function called [dbo].[GetDelimitedStringToTable] which returns the table having two columns ( 1 for Index and 1 for value).

Please find the definition for the function [dbo].[GetDelimitedStringToTable] 

/**********************************************************************

     

Object Name : GetDelimitedStringToTable

Purpose     : Splits the given input delimited string and returns a table.

              Returns 2 columns

                  ID - Represents the IDENTITY column.     

                  Value - Represents the data of a delimited string.

Author      : Karthik D V

Created Date: 06-Jun-2011

Sample Calls:

SELECT * FROM [dbo].[GetDelimitedStringToTable] ( '1,2,3,4', DEFAULT )

SELECT * FROM [dbo].[GetDelimitedStringToTable] ( 'Karthik#Aneesh#Anand#Senthil#Niloy - The Doc#Prasob - Super Singer', '#' )

                       

Modification History:

----------------------------------------------------------------------

Date        Modified By       Modification Details

----------------------------------------------------------------------

----------------------------------------------------------------------

   

**********************************************************************/

CREATE FUNCTION [dbo].[GetDelimitedStringToTable]

(

      @DelimitedString  VARCHAR(8000) ,

      @Delimiter        CHAR(1) = ','

)

RETURNS @ParsedTable TABLE(ID INT IDENTITY(0,1), Value VARCHAR(MAX))

BEGIN

      ;WITH CTE ( StartPos, EndPos ) AS

            (

                  SELECT 1, CHARINDEX(@Delimiter, @DelimitedString)

                  UNION ALL

                  SELECT

                         EndPos + (LEN(@Delimiter)), 

                         CHARINDEX(@Delimiter, @DelimitedString, EndPos

                         + (DATALENGTH(@Delimiter)))

                  FROM CTE

                  WHERE EndPos > 0

            )

            INSERT INTO @ParsedTable ( Value)

            SELECT

                  Value = SUBSTRING ( @DelimitedString, StartPos, CASE WHEN EndPos > 0 THEN EndPos - StartPos ELSE 4000 END )

            FROM CTE

            OPTION (MAXRECURSION 32767)

           

            RETURN

END

GO

Now, lets look at some samples:

SELECT * FROM [dbo].[GetDelimitedStringToTable] ( 'Karthik#Aneesh#Anand#Senthil#Niloy - The Doc#Prasob - Super Singer', '#' )

SELECT * FROM [dbo].[GetDelimitedStringToTable] ( '1,2,3,4', DEFAULT )