CODEWITHSUNDEEP
sqlsql-server-2012
Yuge Chen
Yuge Chen

Reputation: 127

SQL how to take a comma delimited list (sting format) and sort it

I have a table as shown below. The column "newDataIDs" is a comma delimited list but the column itself is in varchar format. How can I sort the values in column in ascending order and put it back to the column in varchar format?

(i.e for 2nd row of the table the expected value for newDataIDs is: "WVR0000015, WVR0000019, WVR0000020")

enter image description here

Edit: Thanks to @METAL,I have gotten the anwer. I have incorporated his answer into my case as follow:

 update [db].[dbo].[mytable] set [NewDataIDs] = t.ids
 from  (
 SELECT STUFF((
          SELECT ',' +ltrim(Split.a.value('.', 'NVARCHAR(MAX)')) sorted
            FROM
            (
                SELECT CAST('<t>'+REPLACE([NewDataIDs], ',', '</t><t>')+'</t>' AS XML) AS String
            ) AS A
            CROSS APPLY String.nodes('/t') AS Split(a)
            ORDER BY sorted asc
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') as ids
        from [db].[dbo].[mytable]
 ) t

Upvotes: 0

Views: 689

Answers (1)

Ed Bangga
Ed Bangga

Reputation: 13006

Here's your script.

declare @str varchar(max)

set @str = 'WVR0000015, WVR0000019, WVR0000020'

SELECT STUFF((
          SELECT ',' +ltrim(Split.a.value('.', 'NVARCHAR(MAX)')) sorted
            FROM
            (
                SELECT CAST('<t>'+REPLACE(@str, ',', '</t><t>')+'</t>' AS XML) AS String
            ) AS A
            CROSS APPLY String.nodes('/t') AS Split(a)
            ORDER BY sorted asc
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

Upvotes: 2

Related Questions