SQL Server – Ordering Combined Number Strings Prior To Column Insert

I have 2 string columns (thousands of rows) with ordered numbers in each string (there can be zero to ten numbers in each string). Example:

+------------------+------------+
|  ColString1      | ColString2 | 
+------------------+------------+
| 1;3;5;12;        | 4;6'       |     
+------------------+------------+
| 1;5;10           | 2;26;      |     
+------------------+------------+
| 4;7;             | 3;         |     
+------------------+------------+

The end result is to combine these 2 columns, sort the numbers in ascending order and then put each number into individual columns (smallest, 2nd smallest etc). e.g. Colstring1 is 1;3;5;12; and ColString2 is 4;6; needs to return 1;3;4;5;6;12; which I then use xml to allocated into columns.

Everthing works fine using xml apart from the step to order the numbers (i.e I’m getting 1;3;5;12;4;6; when I combine the strings i.e. not in ascending order).
I’ve tried put them into a JSON array first to order, thinking I could do a top[1] etc but that did not work. Any help on how to combine the 2 columns and order them before inserting into columns:

Steps so far: Example data:

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, ColString1 VARCHAR(50), ColString2 VARCHAR(50));
INSERT INTO @tbl (ColString1, ColString2)
VALUES
('1;3;5;12;', '4;6;'),
('1;5;10;', '2;26;'),
('14;', '3;8;');

XML Approach (Combines strings and puts into columns but not in the correct order):

   ;WITH Split_Numbers (xmlname)
AS
(
    SELECT 
    CONVERT(XML,'<Names><name>'  
    + REPLACE ( LEFT(ColString1+ColString2,LEN(ColString1+ColString2) - 1),';', '</name><name>') + '</name></Names>') AS xmlname
    FROM @tbl
)

 SELECT    
 xmlname.value('/Names[1]/name[1]','int') AS Number1,    
 xmlname.value('/Names[1]/name[2]','int') AS Number2,
 xmlname.value('/Names[1]/name[3]','int') AS Number3,
 xmlname.value('/Names[1]/name[4]','int') AS Number4,
 xmlname.value('/Names[1]/name[5]','int') AS Number5 
--etc for additional columns
 FROM Split_Numbers

Current Output: numbers not in correct order,

+---------+---------+---------+---------+---------+
| Number1 | Number2 | Number3 | Number4 | Number5 |
+---------+---------+---------+---------+---------+
|       1 |       3 |       5 | 12      | 4       |
|       1 |       5 |      10 | 2       | 26      |
|      14 |       3 |       8 | NULL    | NULL    |
+---------+---------+---------+---------+---------+

Desired Output: numbers in ascending order.

    +---------+---------+---------+---------+---------+
    | Number1 | Number2 | Number3 | Number4 | Number5 |
    +---------+---------+---------+---------+---------+
    |       1 |       3 |       4 | 5       | 6       |
    |       1 |       2 |       5 | 10      | 26      |
    |      3  |       8 |      14 | NULL    | NULL    |
    +---------+---------+---------+---------+---------+

JSON Approach: combines the columns into a JSON array but I still can’t order correctly when in JSON format.

REPLACE ( CONCAT('[',   LEFT(ColString1+ColString2,LEN(ColString1+ColString2) - 1), ']') ,';',',')

Any help will be greatly appreciated whether there is a way to order the xml or JSON string prior to entry. Happy to consider an alternative way if there is an easier solution.

Answer

While waiting for a DDL and sample data population, etc., here is a conceptual example for you. It is using XQuery and its FLWOR expression.

CTE does most of the heavy lifting:

• Concatenates both columns values into one string. CONCAT() function protects against NULL values.
• Converts it into XML data type.
• Sorts XML elements by converting their values to int data type in the FLWOR expression.
• Filters out XML elements with no legit values.

The rest is trivial.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, col1 VARCHAR(100), col2 VARCHAR(100));
INSERT INTO @tbl (col1, col2)
VALUES
('1;3;5;12;', '4;6;'),
('1;5;10;', '2;26;');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ';';

;WITH rs AS
(
    SELECT *
          , CAST('<root><r><![CDATA[' + 
               REPLACE(CONCAT(col1, col2), @separator, ']]></r><r><![CDATA[') + 
             ']]></r></root>' AS XML).query('<root>
                    {
                        for $x in /root/r[text()]
                        order by xs:int($x)
                        return $x
                    }
                    </root>') AS sortedXML
    FROM @tbl
)
SELECT ID
    , c.value('(r[1]/text())[1]','INT') AS Number1
    , c.value('(r[2]/text())[1]','INT') AS Number2
    , c.value('(r[3]/text())[1]','INT') AS Number3
    -- continue with the rest of the columns
FROM rs CROSS APPLY sortedXML.nodes('/root') AS t(c);

Output

+----+---------+---------+---------+
| ID | Number1 | Number2 | Number3 |
+----+---------+---------+---------+
|  1 |       1 |       3 |       4 |
|  2 |       1 |       2 |       5 |
+----+---------+---------+---------+