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

    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

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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

  • 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

  • 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

  • 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)

  • 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

    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

  • ;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?

  • data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAY4AAAAVCAYAAABR0glIAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAAAqsSURBVHhe7ZzPSxxJFMe/7j+wR3eRScBlEDwoQhBB9mBA9CCCmJMhGBAMCYIgKAhz8DAQMBAQwkqEQETiSRkQD8qAHoIgIgzjQQjDDiRDyM4t/gXZ96qre7p7qrqrZyYTE+oDTXdXV3VXV71X70ePdnwjYLFYLBaLIb/d3Nygo6MDdm/3dm/3dm/3JvuOd4UbG3FYLBaLxZjf5N5isVgsFiOs4bBYLBZLIqzh+FWplFGtyGNLNM2Mla5t0nLL7eO2zdUt6o/ecFT+wdrA73iYzcsCpoyjWSqb/QdVWdJOitlRPKQ+vTmTBTrcvvM2O4ojX/3q2aLv2iKO/BPB7UzeLeL+7cQdD97WdsuyVJZPPsHmJJUH5o+gvr8ZWERRnlZ3a/eobbXrNfJ4k6Wx8b+7t416c1LMhq/JjfvhttXKFB1nVc82IPRefrhP/vHxEzlWfhT317VNWl6H6l18clt3T1Fem4N6nLkrntF9z4LjwPrglWme4VIbxybmiZ7BfQ3oXZPUxsDtn4Ou3E9k25brENFg28j+EFEy7hKuU3v3KNlREx1x9A0iXSrVFtLKEc6v3GPDRbZl5HG534uVwg3m7kQ9m5RkMoehgxu8o7rvslM4X5ATQUK7uQVMedcm8DmTVIgj7t9O6F3WMYWX3IdCAUPHA87k07zkSlyexxqXl9bl+8kFenIVJ3wq6ZzJO+/hbgfPkZ6eQL+87lLdXQdGnqGTT/qey+c628tXvahsOfPRn3HL93Afj8V8iXMaZ0GUTKEb4yNALkYBgqjfy4PHY18eh9GOlR/N/XVtk5YH0L0LydzCW6Re8VhSWzxw5pplQNyTyg9WAI0cunPXP5ymOk9qz6U+bS4A94a76UTzDBfuvzeOjcyTQ/H0Gvene3H+PnlbJTo90JX7aasONdE2TnYCc6MhXMdQdlSwsYtJVU1hKJ1Dwe3kpw/A9GOQ+AGpZ1jblgtJO6iUUOnrwR/yVIuoN4WBlDznfhY2aCJo4rauMZSlY+/aKObmSYi3I7y/MNr7t5nhDVqM3fHvxsDYICofSRlpjkrptFf+Z/oCnz85x+PbLJy8oOspbpNRpCgqSB6HL3ppgZGnITqHJ5C6+oD/5Hk0ETLFDC+SAm8kMMRR70VznskhNT0oz0Nox8qP5v66tknLA2iedXaIEzLWE2L8qQ4Z5zk6rn68Rnps3LknyfLE8jUuw4tjYO5I3snI72RkdJdZJUMhZVfzDAfFOCaeJ6aMLyXqS4bk5fioNU6nTg905X7aqkNNtI2UnRgZF9TXMZMdFWUUjg2+cQyM1LwD9haGRnrEMVswz+uvkLfC1tQLe6i+uE5hsAiHyJrRuVeHyl2Bq4Vq1M71YOrqspe/itLVKpao3hoJvDhWRR2pcfKWVnG4mw/mA9mzpUXLW/BdeNHze8Bx6O7PhPrtepq1cXDe0+/xrblhaWzbKI+AJ/MCqbvdjkB0/yXLgT+6FQqjg57Hnk3dGPGioohCXKp03cioS7QyJWDFeGsoxDGcbeA8vYIJdqgVNDNWurZJy03gtvAWjhqdd3tR8hZgkoHyRf09w3NHi+VKmvVoADvpPc846J4hUI5jA/PEOpjmvvyFLkjnQci4T4/95wGdIB3QZhlcanoQRFfu5zvrkAmBtrTmSZ2P7I9ybmptBYo6RrKjQq6j8R/H75BSiwfkcckvdUeWe7A1ewCMFciakkUVYQ+Fw2wRr67RNfKayhfxhRZ7zDt1Xo5dY13k6RxvSKQzDqZQecEejOMJBesCcxzCiRQJhWtZeayMeMiyZ/fQVV7H0mRooVbBEYSxt8zo7u9Y9a4sexW0ZXtwLjw7whuHG6xMX3iLZvV9DiWh1CZt9VFNdfcJduB6i43D/UnN149pWHAhDLhUaNqWOP2XVc2FhhiZSqSoWkh5uF9uiuwnJ41DbxH1ctzCCOScuZjdwGfUe511c0f0z5L+UN1HIc9W+YyIcUw6T0K+Rvg+7N0jJl0VXAc4JexFpRp0emCiH99bh0wItqXoMDaToZsbf1tNHQPZiSLecAgPmxZW9ipVHomwQDTgM9KccfqHFvdxXgw4pcP5U7eOyKWStfubhEB4+eR59L3FOgnwm/dpPD2gl9XWTQD1YTzj5A45/+6E5mHKKLreUgJvWaC6P/f76gI7wpjQJiIk8qrYgLrjQLDSOosmezhwlNewrQqO2DaPpzRGNAlkxI/16agA/m8cB3t4BApzvdDZgDiZUuB9dPcWtGg4r18ZW4xRvJ+HEnowIVIdnOOufX/ol3L4bnsD93DhFEZCCwnJV2p5CueTwShW9YzWjaPj0Z8sOPO49OLC5/EqCK0Dcej0wEQ/fogO1ZG8rcncRNVJLjs14g2H8A6ukdu6lt5CK5F5v+yK9OCj0jGNwfn3+xxRiMXKl1vHv7jcIoO1TV6/4eKlwru/OPN9DBabNKB+vH6wYpBR8EJag7YhWOCXSOCf+gRehKDlf+UZ8B+FoNEhukPDCwQb0flenJwm+E7UgEx5H92NIghnkSq9GKgtUnQc/tVJo2PF6NomLTeB26a7ZT5ajJ3K0+fvBzQdMfcsZh/gZHoPczPP8HTZjfx1z8gbjaMRLO/pPZ98F8jhkM5Rk6j0gNGV+7kVOkREtVX3BwZzY6YHprIjkOuXgeGgjpPXjyuNNRQ34py/7Ax/7+D0jV8g3DryZ38iRSMWa1mXQqvxzGs86rvGF+jqmsGC8JB/Nuqec45XRBRkpPhDeIaMkzAeFBlR+FvZl16/Idr78ztS9HTpjUMod+shw/RMDvA+Tpm29UF1NjnNF1aKOz2+CI0FYhBdMQbIETBg6G+14IQFtw42nvuHiYx+lEwlWVTVuB8ine3l8iDSywWsuVGxS0NjJdG1TVpuAn+H4zSuTInyYiDG52zRk8Xq2QZ2rh7XjWdg7viXNPvkoEjj2zlDOudGL8pnkF5GjGOSeWI9RiBlJvXgI4k/OV6uQ1f99ME5CK0Z/IFYiU4PdOV+2qhD0cS0VfbHRMYj6hjIjhpn3owMR/Qvh6hz2T3g2LFqDyfXgVevQ94y13kObEnLx2GhEF7+mk+LuUjRDOB8jNqldHV9pNJCqJYUH4xZGVbIIro5eM6/r7j59+ENPJ0Hcm5KKPMBQ9MIprJC+ftwakR//9A48L01eX9n0fQLinlbl+L2Kkp4i3XZD96EJ0FzNSVyl6NY4zFNr9CYykY6KrSwjEXUizUMnHIko+9FcwZoZYoVw1SIG4GdFSk32rHy1dGha5u03ORZ7OS8cuWW28qP2pynlrK4tHCN++4vpPx4c1dGkRbpR5wOlpc8uaPIoqp7hpYk86ReGIUeUPk9inycNO0oDk/lRblmVBakTlB0Sk5xHTo90OqHj7bqUBTKtiZyqsNApkxkRwP/hNj+k0OieJZH/7B51PFTwH9lSsrX2YggK+BI6/BuPmYxaQHkCa2dTmDNKCXVIpoZK13bpOXfke8yd22eJ/5DxSV6nlmqskX8gLmK5Bb1xyzi+MX55YwGQ5FbKwWsc2YFOI1JnzVNGUfkcbb9l1DNjJWubdLy70jr56498+T/q26O7JOklFvCD5irSG5Rf2zEYbFYLJZE2IjDYrFYLImwhsNisVgsifn29etXTlfZvd3bvd3bvd3H7L9++x84jn21aQ7S6wAAAABJRU5ErkJggg==

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply