Sort comma separated value in SQL Table
-
Hi,
How do I sort a previously comma separated value in the alphabetical order ?
Astrakhan, Azerbaijan, Kazakhstan, Russia Land, Sakhalin, Uzbekistan, Turkmenistan
Required Result : Astrakhan, Azerbaijan, Kazakhstan, Russia Land, Sakhalin, Turkmenistan, Uzbekistan
Thanks,
PSB -
Have a look at delimitedsplit8k_lead and FOR XML PATH. Have a go, and if you don't succeed post what you tried and what the problem was.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Tuesday, December 4, 2018 6:47 AM
As this is a 2017 forum, this can be done natively:
DECLARE @SomeText VARCHAR(8000) = 'Astrakhan, Azerbaijan, Kazakhstan, Russia Land, Sakhalin, Uzbekistan, Turkmenistan';WITH Split (Value)
AS
(
SELECT TOP 100 PERCENT
*
FROM STRING_SPLIT(REPLACE(@SomeText, ' ', ''), ',') ss
ORDER BY value
)
SELECT STRING_AGG(Split.Value, ', ')
FROM Split;The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I'd use the STRING_SPLIT() here, which is efficient for use. If you need to put this back into the column, you'll need to use string concatenation to get the result
DECLARE @SomeText VARCHAR(8000) = 'Astrakhan, Azerbaijan, Kazakhstan, Russia Land, Sakhalin, Uzbekistan, Turkmenistan';
DECLARE @newtext VARCHAR(8000) = '';
WITH Split (Value)
AS
(
SELECT TOP 100 PERCENT
*
FROM STRING_SPLIT(REPLACE(@SomeText, ' ', ''), ',') ss
ORDER BY value
)
SELECT @newtext = @newtext + STRING_AGG(Split.Value, ', ') + ','
FROM Split;SELECT SUBSTRING(@newtext, 1, LEN(@newtext)-1)
-
The problem with these solutions (mine included) is that they don't work :blush: At least not on my instance.
The CTE produces the correctly ordered resultset.
STRING_AGG then appears to ignore that ordering and put things back together exactly as they were before.
Not sure how it even does that!
Haven't found a straightforward fix yet.The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin - Tuesday, December 4, 2018 8:56 AM
Just found this:
STRING_AGG(Split.Value, ', ') within group ( order by split.value)
seems to deal with order
- Bert-701015 - Tuesday, December 4, 2018 9:04 AM
Thanks for teaching me something new!
Here's a working solution, nice and simple now:
DECLARE @SomeText VARCHAR(8000) = 'Astrakhan, Azerbaijan, Kazakhstan, Russia Land, Sakhalin, Uzbekistan, Turkmenistan';WITH Split (Value)
AS
(
SELECT ss.value
FROM STRING_SPLIT(REPLACE(@SomeText, ' ', ''), ',') ss
)
SELECT STRING_AGG(Split.Value, ', ') WITHIN GROUP (ORDER BY Split.Value)
FROM Split;The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Here is what I did on the subject
😎
Reaping the benefits of the Window functions in T-SQLCREATE FUNCTION [dbo].[DelimitedSplit8K_LEAD]
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table? produces values from 0 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT t.N+1
FROM cteTally t
WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF((LEAD(s.N1,1,1) OVER (ORDER BY s.N1) - 1),0)-s.N1,8000))
FROM cteStart s
;
GO - Eirikur Eiriksson - Tuesday, December 4, 2018 9:09 AM
And before he published that article, he sent it to me for review and I have verified that it's twice as fast as the original because of the use of LEAD instead of doing the CHARINDEX thing.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks ! This worked perfectly .
DECLARE @SomeText VARCHAR(8000) = 'Astrakhan, Azerbaijan, Kazakhstan, Russia Land, Sakhalin, Uzbekistan, Turkmenistan';
WITH Split (Value)
AS
(
SELECT ss.value
FROM STRING_SPLIT(REPLACE(@SomeText, ' ', ''), ',') ss
)
SELECT STRING_AGG(Split.Value, ', ') WITHIN GROUP (ORDER BY Split.Value)
FROM Split; -
I need to grab these values from a certain column of a table . Do I need to store these values in variable then USE STRING_SPLIT with sql CURSOR ?
Thanks,
PSB - PSB - Tuesday, December 4, 2018 9:41 AM
No, STRING_SPLIT is a table value function, if it's a column in a table you reference it in the from using CROSS APPLY. In simple terms, your query will look a little like this:
...
FROM YourTable YT
CROSS APPLY STRING_SPLIT(YT.DelimitedColumn, ',') SS
...Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
;WITH Split (Value)
AS
(
SELECT ss.Country
FROM [dbo].[Exceptions] YT CROSS APPLY STRING_SPLIT(REPLACE([Country], ' ', ''), ',') ss
)
SELECT STRING_AGG(Split.Value, ', ') WITHIN GROUP (ORDER BY Split.Value)
FROM Split;
Getting this error :Invalid object name 'STRING_SPLIT'.
-
What version of SQL Server are you using?
-
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply