-2

I have a column of strings in SQL Server 2019 that I want to sort

Select * from ID 

[7235, 6784] 
[3235, 2334] 
[9245, 2784] 
[6235, 1284]

Trying to get the result below:

[6784, 7235]
[2334, 3235]
[2784, 9245]
[1284, 6235]
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Poola Tony
  • 23
  • 2
  • 3
    Documentation for SELECT queries should cover this, as well as many intro SQL tutorials. Please take a look at those first and come back if you need further help. – siride Jan 26 '22 at 01:57
  • 2
    So you want to update existing data and reverse the order of the numbers in the column if they are not ascending? Is there only ever one pair? (i.e. one `,`) in the column? Are the numbers always four characters? – Nick.Mc Jan 26 '22 at 02:35
  • there might be any number of items i.e. more than one comma or even just square brackets [] or null. and any number of digits. I tried a case statement separating the values if lenght more than 3 then rearranged but it's untidy af. – Poola Tony Jan 27 '22 at 00:42

2 Answers2

2

Given this sample data:

CREATE TABLE dbo.ID(ID int IDENTITY(1,1), SomeCol varchar(64));

INSERT dbo.ID(SomeCol) VALUES
('[7235, 6784]'),
('[3235, 2334]'), 
('[9245, 2784]'),
('[6235, 1284]');

You can run this query:

;WITH cte AS 
(
  SELECT ID, SomeCol, 
      i = TRY_CONVERT(int, value), 
      s = LTRIM(value)
    FROM dbo.ID CROSS APPLY 
    STRING_SPLIT(PARSENAME(SomeCol, 1), ',') AS s
)
SELECT ID, SomeCol, 
  Result = QUOTENAME(STRING_AGG(s, ', ') 
           WITHIN GROUP (ORDER BY i))
FROM cte
GROUP BY ID, SomeCol
ORDER BY ID;

Output:

ID SomeCol Result
1 [7235, 6784] [6784, 7235]
2 [3235, 2334] [2334, 3235]
3 [9245, 2784] [2784, 9245]
4 [6235, 1284] [1284, 6235]
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
0

The source table has a column with a JSON array.

That's why it is a perfect case to handle it via SQL Server JSON API.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID int IDENTITY PRIMARY KEY, jArray NVARCHAR(100));
INSERT @tbl (jArray) VALUES
('[7235, 6784]'),
('[3235, 2334]'), 
('[9245, 2784]'),
('[6235, 1284]');
-- DDL and sample data population, end

SELECT t.*
    , Result = QUOTENAME(STRING_AGG(j.value, ', ') 
           WITHIN GROUP (ORDER BY j.value ASC))
FROM @tbl AS t
    CROSS APPLY OPENJSON(t.jArray) AS j
GROUP BY t.ID, t.jArray
ORDER BY t.ID;

Output

+----+--------------+--------------+
| ID |    jArray    |    Result    |
+----+--------------+--------------+
|  1 | [7235, 6784] | [6784, 7235] |
|  2 | [3235, 2334] | [2334, 3235] |
|  3 | [9245, 2784] | [2784, 9245] |
|  4 | [6235, 1284] | [1284, 6235] |
+----+--------------+--------------+
miriamka
  • 459
  • 5
  • 9