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 (https://blog.sqlauthority.com)
20 Comments. Leave new
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.!
Was woundering the same myself Jayaram, did you work out how to use the function against SELECTED data?
Kind Regards Guy
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
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:
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
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’, ‘,’)
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
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.
Hi,
Option 2 is better because function is not used in subquery.
Option 1 has a lot of overhead.
After all the best is one that pass testing.
Regards,
Pinal Dave
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
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.
Good Post!
thanks a lot pinal.
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 ,’,’)
What did you mean by NULL values?
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)
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
CREATE FUNCTION [dbo].[str__split](
@str NVARCHAR(MAX)
,@delimiter NVARCHAR(MAX)
)
RETURNS @split TABLE(
[str] NVARCHAR(MAX)
)
AS
BEGIN
INSERT INTO @split
(
[str]
)
SELECT
[x].[c].[value](‘(./text())[1]’, ‘nvarchar(4000)’)
FROM
(
SELECT
[x] = CONVERT(XML, ‘‘ + REPLACE(@str, @delimiter, ‘‘) + ‘‘).[query](‘.’)
) AS [a]
CROSS APPLY
[x].[nodes](‘i’) AS [x]([c]);
RETURN;
END
GO