Pages

Wednesday, February 17, 2010

Tsql Query to Sort Numbers in a comma separated string

/*
* Query to order numbers in asc order.
* Numbers are given as a string separated by a comma
* */
DECLARE @numbers VARCHAR(50)='23,2000,45,7,3,0,24,67,676,45,4,2,230';
DECLARE @finalString VARCHAR(max);--used to hold the final string
DECLARE @ProductTotals TABLE--Dynamic table to hold all the values numeric values
(

ProductID DECIMAL(18,2)

)

DECLARE @currentNumber DECIMAL(18,2)--variable to hold the current number that
--is reaped from the number array
WHILE( PATINDEX('%,%',@numbers)<>0)
BEGIN

SET @currentNumber=CAST(SUBSTRING(@numbers,0,PATINDEX('%,%',@numbers)) AS DECIMAL(18,2));

INSERT INTO @ProductTotals VALUES (@currentNumber);

SET @numbers=SUBSTRING(@numbers,PATINDEX('%,%',@numbers)+1,(LEN(@numbers)+1)-PATINDEX('%,%',@numbers));


END
SET @finalString='';
SELECT @finalString=@finalString +cast(pt.ProductID AS VARCHAR(10))+',' FROM @ProductTotals pt
ORDER BY pt.ProductID ASC

PRINT @finalString

/*
*The result will appear as follows
*0.00,2.00,3.00,4.00,7.00,23.00,24.00,45.00,45.00,67.00,676.00,2000.00,
*
*/

0 comments:

Post a Comment