December 4, 2018 at 6:44 am
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
December 4, 2018 at 7:44 am
Thom A - Tuesday, December 4, 2018 6:47 AMHave 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.
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 4, 2018 at 8:37 am
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)
December 4, 2018 at 8:56 am
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 4, 2018 at 9:04 am
Phil Parkin - Tuesday, December 4, 2018 8:56 AMThe 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.
Just found this:
STRING_AGG(Split.Value, ', ') within group ( order by split.value)
seems to deal with order
December 4, 2018 at 9:09 am
Bert-701015 - Tuesday, December 4, 2018 9:04 AMPhil Parkin - Tuesday, December 4, 2018 8:56 AMThe 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.Just found this:
STRING_AGG(Split.Value, ', ') within group ( order by split.value)
seems to deal with order
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 4, 2018 at 9:09 am
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
December 4, 2018 at 9:16 am
Eirikur Eiriksson - Tuesday, December 4, 2018 9:09 AMHere 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
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
Change is inevitable... Change for the better is not.
December 4, 2018 at 9:36 am
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;
December 4, 2018 at 9:41 am
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
December 4, 2018 at 9:46 am
PSB - Tuesday, December 4, 2018 9:41 AMI 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
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
December 4, 2018 at 9:51 am
;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'.
December 4, 2018 at 9:57 am
What version of SQL Server are you using?
December 4, 2018 at 10:10 am
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply