Convert comma seperated string to table column MS sql server

SurajRane
Posted by SurajRane under Sql Server category on | Views : 4399
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

Comments or Responses

Login to post response