Thursday, March 10, 2011

Saperating coma separated values in a string using T-SQL ; SQL Server

If you do lot of application development, you often come across to a situation where we need to split values of a string with a delimiter. The delimiter could be coma. Here is the T-SQL script on how to do it.

Declare @OrderList varchar(500) = '1,2,3,4,78,987'

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
                   -- Here we can write necessary code with OrderId
                   print @OrderId
               END
          SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
          SET @Pos = CHARINDEX(',', @OrderList, 1)
   END
END