Tuesday 20 March 2012

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

 

 

 

 

 

 

 

 

 

4 comments:

  1. give this one a try...

    create function dbo.fnSplit
    ( @list varchar(max)
    , @delimiter varchar(10) )
    returns table
    /*
    ————————————————————————————————————————————————————————————————————————————————————————————————————
    Purpose : Divides the @list parameter at the @delimiter into an enumerated table.
    Parameters : @list - the delimited list of values up to 8000 characters long
    @delimeter - the delimter value
    Calling : select Value, RowId from dbo.fnSplit(@list, @delimeter);
    Returns : Table
    History :
    Date Developer Wk Itm Description
    —————————— —————————————————— —————— ————————————————————————————————————————————————————————————
    12/08/2011 Paul Hunter 0 Initial function creation
    ————————————————————————————————————————————————————————————————————————————————————————————————————
    */
    return( select cast(v2.l.value('.', 'varchar(8000)') as varchar(8000)) as Value
    , row_number() over ( order by r.row ) as RowId
    from( select( select convert(xml, ''
    + replace(@list, @delimiter, '')
    + '') ) ) as v1(x)
    cross apply x.nodes('/l/v') v2(l)
    cross apply ( select 1 as row ) r );
    go

    ReplyDelete
  2. this is the one I use. it creates a tvf. go to the link, for an explanation. it is extremely fast and efficient.
    --http://www.sqlservercentral.com/articles/Tally+Table/72993/
    -- Jeff Moden
    CREATE FUNCTION dbo.udfDelimitedSplit8K
    --===== Define I/O parameters
    (@pString VARCHAR(8000),
    @pDelimiter CHAR(1))
    RETURNS TABLE WITH SCHEMABINDING AS
    RETURN
    --===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
    -- enough to cover VARCHAR(8000)
    WITH E1(N) AS (
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1
    ), --10E+1 or 10 rows
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
    -- for both a performance gain and prevention of accidental "overruns"
    SELECT 0 UNION ALL
    SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    ),
    cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
    SELECT t.N+1
    FROM cteTally t
    WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
    )
    --===== Do the actual split. The ISNULL/NULLIF combo handles the length for
    --===== the final element when no delimiter is found.
    SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
    Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
    FROM cteStart s
    ;
    GO

    ReplyDelete