Code Snippet posted by:
SurajRane | Posted on: 3/15/2010 | Category:
SQL Server Codes | Views: 3083 | Status:
[Member]
|
Alert Moderator
Convert CSV to column
Many times we need to pass comma seperated list of some items to stored procedure or say database.
And need a way to convert passsed comma seperated list to column. Below is function to convert such a scv list to column.
CREATE function ParseCsvToColumn
(
@OrderList varchar(8000)
)
returns @tbl table (id INT)
as
/*
select * from dbo.Ufn_ParseCsvToColumn (' , 1,2,3,4,6,, ')
*/
BEGIN
DECLARE @OrderID varchar(10), @Pos INT
SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)
IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @tbl (id) VALUES (CAST(@OrderID AS int)) END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)
END
END
RETURN
END