Comma delimited string to table

 

Here I am creating a simple function that converts a comma delimited string to table. It takes 2 arguments. One is the string and second is the delimited character. You can use any character as delimited character.

The function is ready to use. Just copy it and you can directly use it.

/*

    The function takes the string and string seperator like ', or | or anything'

    and returens a table.

    last update: 20-March-2012

    by: joydeep das

 

*/

 

IF OBJECT_ID (N'fn_CnvtTbl') IS NOT NULL

    DROP FUNCTION dbo.fn_CnvtTbl

   

GO

CREATE FUNCTION fn_CnvtTbl

    (

        @sStingVal  VARCHAR(8000) = NULL,

      @sSeperator CHAR(1) = '|'

      )

      RETURNS

            @myTBL TABLE (TblVal VARCHAR(1000))

AS

BEGIN

      DECLARE @CurrentStr VARCHAR(2000)

      DECLARE @ItemStr VARCHAR(200)

     

      IF ISNULL(@sStingVal,'')='' OR ISNULL(@sSeperator,'')=''

         BEGIN

             INSERT @myTBL (TblVal) VALUES ('Parameters not properly specified')

             RETURN

         END

        

      SET @CurrentStr = @sStingVal

     

       

      WHILE Datalength(@CurrentStr) > 0

      BEGIN

            IF CHARINDEX(@sSeperator, @CurrentStr,1) > 0

               BEGIN

                  SET @ItemStr = SUBSTRING (@CurrentStr, 1, CHARINDEX(@sSeperator, 

                                            @CurrentStr,1) - 1)

                  SET @CurrentStr = SUBSTRING (@CurrentStr, CHARINDEX(@sSeperator,

                                               @CurrentStr,1) + 1,

                                              (Datalength(@CurrentStr) -

                                               CHARINDEX(@sSeperator, @CurrentStr,1) + 1))

                  INSERT @myTBL (TblVal) VALUES (@ItemStr)

                END

             ELSE

                BEGIN               

                  INSERT @myTBL (TblVal) VALUES (@CurrentStr)               

                  BREAK;

                END

      END

      RETURN

END

 

/*

to test the function use

 

SELECT * FROM dbo.fn_CnvtTbl('RAM,SHAM,JADU,MADHU',',')

*/   

 

Hope you like it.

 

Posted by: MR. JOYDEEP DAS