Given
DECLARE @InvoiceNo nvarchar(MAX)
SET @InvoiceNo='10,1,2,3,4,5'
SELECT @InvoiceNo
--10,1,2,3,4,5
How to sort it ascendingly to get
--1,2,3,4,5,10
-
3If at all possible, change your design so that you store multiple values in a type designed for holding multiple values, such as separate rows in a table, or JSON or XML. The start and end of problems you'll have here will always be the decision to cram these multiple values into a string.Damien_The_Unbeliever– Damien_The_Unbeliever2022-01-27 09:18:15 +00:00Commented Jan 27, 2022 at 9:18
Add a comment
|
1 Answer
Assuming you are using SQL Server 2017 or later, we can use a combination of STRING_SPLIT and STRING_AGG here:
DECLARE @InvoiceNo nvarchar(MAX)
SET @InvoiceNo='10,1,2,3,4,5'
WITH cte AS (
SELECT value
FROM STRING_SPLIT(@InvoiceNo, ',')
)
SELECT STRING_AGG(value, ',') WITHIN GROUP (ORDER BY CAST(value AS int)) AS InvoiceNo
FROM cte;
