Topic : Conversion Of comma separated value into a Table Valued function (Splitting up of comma separated Values).

This post actually helps for the developers when they have a requirement such as, the data coming in with a pipe separated value or a comma separated value, and these values has to converted into a table data . So especially whenever their is a requirement for conversion of string data into a table then any of the below logic helps in this conversion

There are two ways where u can achieve this functionality.

1. Can be achieved by creating a Table Valued function

2. Can be achieved by XML functions.

Type-1 : Creation of Function for Split

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Split]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[Split]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [DBO].[SPLIT](@INPSTRNG NVARCHAR(MAX), @DELIMITER CHAR(1))        

RETURNS @TABLEVALUED TABLE (SPLITTEDVALUES NVARCHAR(40))        

AS        

BEGIN        

    DECLARE @STARTER INT        

    DECLARE @DATA NVARCHAR(MAX)        

       

    SELECT @STARTER = 1        

        IF LEN(@INPSTRNG)<1 OR @INPSTRNG IS NULL  RETURN        

       

    WHILE @STARTER!= 0        

    BEGIN        

        SET @STARTER = CHARINDEX(@DELIMITER,@INPSTRNG)        

        IF @STARTER!=0        

            SET @DATA = LEFT(@INPSTRNG,@STARTER - 1)        

        ELSE        

            SET @DATA = @INPSTRNG        

           

        IF(LEN(@DATA)>0)   

            INSERT INTO @TABLEVALUED(SPLITTEDVALUES) VALUES(@DATA)        

  

        SET @INPSTRNG = RIGHT(@INPSTRNG,LEN(@INPSTRNG) - @STARTER)        

        IF LEN(@INPSTRNG) = 0 BREAK        

    END    

RETURN        

END

EXAMPLE:

1.SELECT * FROM [DBO].[SPLIT]('A,B,C,D,E,F',',')

2.SELECT * FROM [DBO].[SPLIT]('11|22|33|44|55|66|77','|')

Type-2 : Splitting up of Comma separated data using XML functions.

Sometimes if there are any constraints for creating a function for splitting up of data then here is another way where we can achieve it using XML functions.

EXAMPLE: 1

Using single Value

Declare @CommSepVal VARCHAR(500)='AA,BB,CC,DD,FF,GG,HH,LL',

@Xml  Xml

Set  @Xml = N'<root><r>' + replace(@CommSepVal,',','</r><r>') + '</r></root>'

Select * 

  From

(

Select Val.value('.','varchar(150)') As Data

 From @Xml.nodes('//root/r') As A(Val)

) Fin

EXAMPLE: 2

Table Values with Commaseparated Values

DECLARE @TABLE TABLE

(

ID INT,

VLUES VARCHAR(100)

)

INSERT @TABLE

VALUES (1,'AA,BB,CC,DD'),(2,'FF,GG,HH,LL'),(3,'W,E')

SELECT ID,DATA FROM (SELECT ID,CONVERT(XML,N'<ROOT><R>' + REPLACE(VLUES,',','</R><R>') + '</R></ROOT>') AS VALUE FROM @TABLE) AS A

CROSS APPLY (

SELECT * 

  FROM

(

SELECT Val.value('.','VARCHAR(150)') AS DATA

 FROM A.value.nodes('//ROOT/R') AS A(Val)

) FIN ) AS B