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://www.SQLAuthority.com)
pinaldave
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: http://sudev.blogspot.com/2008/05/comma-separated-list-of-values-of.html
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