Splitting string in sql server

Splitting a String using Sql Server query in Sql Server


declare @OrderList as varchar(500)
set @OrderList='235,Ravi,SNY,Advanced Development,0.3,0.7,0.5,0.5,0.5,0.5,0.5,0.5,0.5,0.5,0.5,0.5,02/17/2010;
194,Chris Edwards,SNY,CAD,0,0,0,0,0,0,0,0,0,0,0,,11/27/2009;
41,Henry Nguyen,SNY,Mechanical,0,0,0,0,0,0,0,0,0,0,0,,11/25/2009;
97,Baber Raza,SNY,Product Engineering,0,0,0.1,0,0,0,0,0,0,0,0,,11/25/2009;
100,Steve Rojas,SNY,Product Engineering,0,0,0,0,0,0,0,0,0,0,0,,11/29/2009;
101,Haijiang Zhang,SNY,Product Engineering,0,0,0,0,0,0,0,0,0,0,0,,11/26/2009;
116,Benny Tan,SGP,Product Engineering,0,0,0,0,0,0,0,0,0,0,0,,11/26/2009;
117,Jen Kwang Han,SGP,Product Engineering,0,0,0,0,0,0,0,0,0,0,0,,11/25/2009;
59,Shanker Maduri,SNY,Program Manager,0,0,0,0,0,0,0,0,0,0,0,,11/26/2009
;'
SET NOCOUNT ON

DECLARE @TempList table
(
OrderID varchar(30)
)

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 @TempList (OrderID) VALUES (CAST(@OrderID AS varchar(30))) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)

END
END

select * from @TempList