how to reverse comma seprated values.

Question

hi,

i have coma separated vales in a table. what i want to reverse it like 'CLP,CAD,GBP' pls tell me how it can be done with out using xml in efficent way.

DECLARE @TBL AS TABLE
(COLUMN1  NVARCHAR(100))

INSERT INTO @TBL 
SELECT 'AUD,BRL,GBP,CAD,CLP'

SELECT COLUMN1 FROM @TBL

Answer 1

Check an efficient method without XML:

select *, (select string_agg(value, ',') within group (order by ordinal desc) from string_split(COLUMN1, ',', 1)) as RESULT
from @TBL

Answer 2

Hello, you can use below code

SELECT   value FROM @TBL     CROSS APPLY STRING_SPLIT(COLUMN1, ',');

Answer 3

Hello,

You can use below code

SELECT   value FROM @TBL     CROSS APPLY STRING_SPLIT(COLUMN1, ',');

Answer 4

Hi @Rajesh Kumar Yadav

Currently the enable_ordinal in STRING_SPLIT function is only available in Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics (serverless SQL pool only), and SQL Server 2022 (16.x) and later versions.

If SQL Server 2022 (16.x) and later, please refer to Viorel's answer above.

If you are using version between SQL Server 2017 (14.x) and SQL Server 2022 (16.x), then try this query:

SELECT STRING_AGG(value, ',') WITHIN GROUP (ORDER BY [key] DESC) AS New_Column1
FROM @TBL CROSS APPLY OPENJSON(N'["' + REPLACE(COLUMN1, ',', N'","') + N'"]') AS x

If your version is before SQL Server 2017 (14.x), then you need to consider xml way.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.