CODEWITHSUNDEEP
sql-server
Wei Chen Chen
Wei Chen Chen

Reputation: 88

T-SQL Sort nvarchar that consists of comma

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

Upvotes: 0

Views: 47

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

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;

Upvotes: 2

Related Questions