Sort comma separated value in SQL Table

• PSB

  SSChampion

  Points: 13195

  More actions

  December 4, 2018 at 6:44 am

  #369219

  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

• Thom A

  SSC Guru

  Points: 99133

  More actions

  December 4, 2018 at 6:47 am

  #2015428

  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

• Phil Parkin

  SSC Guru

  Points: 246985

  More actions

  December 4, 2018 at 7:44 am

  #2015439

  Thom A - Tuesday, December 4, 2018 6:47 AM

  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.

  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

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736478

  More actions

  December 4, 2018 at 8:37 am

  #2015446

  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)

• Phil Parkin

  SSC Guru

  Points: 246985

  More actions

  December 4, 2018 at 8:56 am

  #2015450

  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

• Bert-701015

  SSCommitted

  Points: 1615

  More actions

  December 4, 2018 at 9:04 am

  #2015452

  Phil Parkin - Tuesday, December 4, 2018 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.

  Just found this: 

  STRING_AGG(Split.Value, ', ') within group ( order by split.value)

  seems to deal with order

• Phil Parkin

  SSC Guru

  Points: 246985

  More actions

  December 4, 2018 at 9:09 am

  #2015454

  Bert-701015 - Tuesday, December 4, 2018 9:04 AM

  Phil Parkin - Tuesday, December 4, 2018 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.

  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

• Eirikur Eiriksson

  SSC Guru

  Points: 182916

  More actions

  December 4, 2018 at 9:09 am

  #2015455

  Here is what I did on the subject
  😎
  Reaping the benefits of the Window functions in T-SQL
  CREATE 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

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  December 4, 2018 at 9:16 am

  #2015459

  Eirikur Eiriksson - Tuesday, December 4, 2018 9:09 AM

  Here is what I did on the subject
  😎
  Reaping the benefits of the Window functions in T-SQL
  CREATE 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

  ---

  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)
  

• PSB

  SSChampion

  Points: 13195

  More actions

  December 4, 2018 at 9:36 am

  #2015462

  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;

• PSB

  SSChampion

  Points: 13195

  More actions

  December 4, 2018 at 9:41 am

  #2015463

  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

• Thom A

  SSC Guru

  Points: 99133

  More actions

  December 4, 2018 at 9:46 am

  #2015465

  PSB - Tuesday, December 4, 2018 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

  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

• PSB

  SSChampion

  Points: 13195

  More actions

  December 4, 2018 at 9:51 am

  #2015466

  ;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'.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736478

  More actions

  December 4, 2018 at 9:57 am

  #2015467

  What version of SQL Server are you using?

• PSB

  SSChampion

  Points: 13195

  More actions

  December 4, 2018 at 10:10 am

  #2015469