SQL SERVER – UDF – Function to Convert List to Table

Following Users Defined Functions will convert list to table. It also supports user defined delimiter. Following UDF is written for SQL SERVER 2005. It will also work well with very big TEXT field. If you want to use this on SQL SERVER 2000 replace VARCHAR(MAX) with VARCHAR(8000) or any other varchar limit. It will work with INT as well as VARCHAR.

CREATE FUNCTION dbo.udf_List2Table
(
@List VARCHAR(MAX),
@Delim CHAR
)
RETURNS
@ParsedList TABLE
(
item VARCHAR(MAX)
)
AS
BEGIN
DECLARE
@item VARCHAR(MAX), @Pos INT
SET
@List = LTRIM(RTRIM(@List))+ @Delim
SET @Pos = CHARINDEX(@Delim, @List, 1)
WHILE @Pos > 0
BEGIN
SET
@item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
IF @item <> ''
BEGIN
INSERT INTO
@ParsedList (item)
VALUES (CAST(@item AS VARCHAR(MAX)))
END
SET
@List = RIGHT(@List, LEN(@List) - @Pos)
SET @Pos = CHARINDEX(@Delim, @List, 1)
END
RETURN
END
GO

Run following script to test above UDF.
----Example 1 for VARHCAR
SELECT item AS Example1
FROM dbo.udf_List2Table('first||2nd||III||1+1+1+1','||')
GO
----Example 2 for INT
SELECT CAST(item AS INT) AS Example2
FROM dbo.udf_List2Table('111,222,333,444,555',',')
GO

ResultSet:
Example1
————-
first
2nd
III
1+1+1+1

(4 row(s) affected)

Example2
————-
111
222
333
444
555

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

22 thoughts on “SQL SERVER – UDF – Function to Convert List to Table

  1. Thanks !! It works fine with data passed as parameter…
    But how to substitute a column name instead of the raw data in the UDF. I tried but in vain… I mean…

    SELECT CAST(item AS INT) AS Example2
    FROM dbo.udf_List2Table(select canview from table3,’,’)
    GO

    gives the error message. Incorrect syntax near “select”

    pL help.!

  2. Was woundering the same myself Jayaram, did you work out how to use the function against SELECTED data?

    Kind Regards Guy

  3. For Jamaram
    Just in case your reading this Jamaram, I did a little work around to accomplish what you needed.
    Declare a varable and select your comma separated list into in then run the function against the varable like below.

    If this is any help then im glad, and im not very good at SQL so I have no idea if this is a messy way to do it etc.

    Declare @yourlist char(800)
    select @yourlist = (SELECT itemlist FROM tablename WHERE id = 1 )

    SELECT item AS Example1
    FROM dbo.udf_List2Table(@yourlist,’,’)
    GO

    Kind Regards Guy

  4. Hi Jayaram & HAGster,

    I guess what you people need is from a table you need to combine all the values in single field e.g. from PRODUCT table you want list of [SKU] column in ‘,’ separated list. for that you can use following T-SQL snippet.

    DECLARE @commaSeparatedVal AS VARCHAR(MAX);
    SELECT @commaSeparatedVal = ISNULL(@commaSeparatedVal +’,’,”) + CONVERT(VARCHAR,[SKU]) FROM PRODUCT
    PRINT @commaSeparatedVal

    Now you can fire

    SELECT CAST(item AS INT) AS Example2
    FROM dbo.udf_List2Table(@commaSeparatedVal,’,’)

    ref: http://sudev.blogspot.com/2008/05/comma-separated-list-of-values-of.html

  5. I am relatively new to SQL, this code from Sudev gandhi works great, but can someone please explain how this works…

    DECLARE @commaSeparatedVal AS VARCHAR(MAX);
    SELECT @commaSeparatedVal = ISNULL(@commaSeparatedVal +’,’,”) + CONVERT(VARCHAR,[SKU]) FROM PRODUCT
    PRINT @commaSeparatedVal

  6. Pingback: SQL SERVER - Function to Convert List to Table Journey to SQL Authority with Pinal Dave

  7. Pingback: SQL SERVER - Function to Convert List to Table Journey to SQL Authority with Pinal Dave

  8. Thanks a ton Dave, I have got stupid extension for more lists of csv into single table :)
    CREATE FUNCTION dbo.udf_Lists2Table
    (
    @List1 VARCHAR(MAX),
    @List2 VARCHAR(MAX),
    @List3 VARCHAR(MAX),
    @Delim CHAR
    )
    RETURNS
    @ParsedList TABLE
    (
    item1 VARCHAR(MAX),
    item2 VARCHAR(MAX),
    item3 VARCHAR(MAX)
    )
    AS
    BEGIN
    DECLARE @item1 VARCHAR(MAX), @Pos1 INT,
    @item2 VARCHAR(MAX), @Pos2 INT,
    @item3 VARCHAR(MAX), @Pos3 INT
    SET @List1 = LTRIM(RTRIM(@List1))+ @Delim
    SET @List2 = LTRIM(RTRIM(@List2))+ @Delim
    SET @List3 = LTRIM(RTRIM(@List3))+ @Delim
    SET @Pos1 = CHARINDEX(@Delim, @List1, 1)
    SET @Pos2 = CHARINDEX(@Delim, @List2, 1)
    SET @Pos3 = CHARINDEX(@Delim, @List3, 1)
    WHILE (@Pos1 > 0 AND @Pos2 > 0 AND @Pos2 > 0)
    BEGIN
    SET @item1 = LTRIM(RTRIM(LEFT(@List1, @Pos1 – 1)))
    SET @item2 = LTRIM(RTRIM(LEFT(@List2, @Pos2 – 1)))
    SET @item3 = LTRIM(RTRIM(LEFT(@List3, @Pos3 – 1)))
    IF @item1 = ”
    BEGIN
    SET @item1 = NULL
    END
    IF @item2 = ”
    BEGIN
    SET @item2 = NULL
    END
    IF @item3 = ”
    BEGIN
    SET @item3 = NULL
    END
    INSERT INTO @ParsedList (item1, item2, item3)
    VALUES (CAST(@item1 AS VARCHAR(MAX)), CAST(@item2 AS VARCHAR(MAX)), CAST(@item3 AS VARCHAR(MAX)))
    SET @List1 = RIGHT(@List1, LEN(@List1) – @Pos1)
    SET @List2 = RIGHT(@List2, LEN(@List2) – @Pos2)
    SET @List3 = RIGHT(@List3, LEN(@List3) – @Pos3)
    SET @Pos1 = CHARINDEX(@Delim, @List1, 1)
    SET @Pos2 = CHARINDEX(@Delim, @List2, 1)
    SET @Pos3 = CHARINDEX(@Delim, @List3, 1)
    END
    RETURN
    END
    GO

    select * from udf_List2Table(’1, ,3′,’a,b,c’,’x, ,z’, ‘,’)

  9. Thank you for this! It really was useful for me, as I wanted to be able to return an Max and a Sum of a group for items that were being calculated, appearing in a single row.

    I changed your function to have the Item be a float, and then used the convert function to pass the variables in:

    Select Val1, Val2, Val3, Val4,
    (
    Select Max(Item) From dbo.udf_List2Table(Convert(varchar(20),Val1) + ‘,’ + Convert(varchar(20),Val2) + ‘,’ + + Convert(varchar(20),Val3) + ‘,’ + + Convert(varchar(20),val4) + ‘,’,’,’)
    ) as MaxVal
    From tbl

  10. Hi Pinal,

    I would like to know which method out of these two options is the best ,

    (option 1)
    select c1,
    from t1
    where t1.c2 in (select cast(item as int) from dbo.udf_List2Table(’123,453,34′,’,’))

    or
    (option 2)
    select c1
    from t1
    where charindex(t1.c2,’123,453,34′) > 0

    as per my understanding , option 2 will supress index usage and will slow the query down when data load is high on t1 table. it force index or table scans as well. Am I correct here or is there more to this ?

    Thanks,
    RG

    • Hi RG,

      I think your solution will fail in cases where there are values like 34,134,3,4
      Please check and confirm.

      Thanks,
      Yashpal.

  11. Hello,

    I am new to SQL and I am looking for a solution for the opposite situation. I have a table, but I need a string that contains each elemont of one column of my table.

    Do you have a script for that?

    Thanks,
    Balázs

  12. HI.
    Is there any other way to do this same thing..? i mean any fast way without Loop.. coz if our string is too long then it effects performance.

  13. Dear Pinal,

    Could you please tell us how to retrieve the null values
    Ex: 1 st row 12,43,12,65
    2 nd row null
    3 rd row 67,1,14
    result set should be like 12
    43
    12
    65
    null
    67
    1
    14

    I have tried in below but in vain

    DECLARE @commaSeparatedVal AS varchar(max);

    SELECT @commaSeparatedVal = @commaSeparatedVal +’,’+ null + CONVERT(varchar,sourcelangid) FROM ProjectSummaries
    PRINT @commaSeparatedVal

    select cast(item as varchar) as sudha from dbo.udf_List2Table (@commaSeparatedVal ,’,’)

  14. Pingback: SQL SERVER – Weekly Series – Memory Lane – #028 | SQL Server Journey with SQL Authority

  15. Hi, the function is returning the same result set even if one | is used in the string. Bug?

    SELECT item AS Example1
    FROM dbo.udf_List2Table(‘first|2nd|III|1+1+1+1′,’||’)
    GO

    Example1
    —————
    first
    2nd
    III
    1+1+1+1

    (4 row(s) affected)

  16. Hi everyone,

    I have a table with the following structure:
    myTable(ID Int, Old_ids VARCHAR(1000));

    SQL Script to simulate my table:
    CREATE TABLE myTable(id INT, old_ids VARCHAR(1000))
    INSERT INTO myTable(id, old_ids) VALUES (1, ’100,200,300′);
    INSERT INTO myTable(id, old_ids) VALUES (2, ’400,500,600′);

    select * from myTable

    ID old_ids
    1 100,200,300
    2 400,500,600

    I would like to generate a [temp] table with the following output:

    ID OLD_ID
    1 100
    1 200
    1 300
    2 400
    2 500
    3 600

    Could you please advise what is the best way to do this in a SQL query?

    Thanks,
    Sathish

    • Hi,

      I have managed to work out the SQL with thhe help of SQL functiondbo.udf_List2Table (which is illustrated above).

      SELECT myTable.id [ID], t1.csvItem [OLD_ID]
      FROM myTable
      CROSS APPLY dbo.ReturnTableFromCSV(myTable.old_ids, ‘,’) AS t1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s