How to sort varchar field with letters and numbers in sql server

Question

I have the following query below and would like to sort according to the image, however the numbers.

WITH cteNiveis(IdConta, NivelRec, NomeTab, Caminho, Nivel, Tipo, Status, Nome) AS
    (
        SELECT CO.Id, 1 , CAST(CO.Nome AS VARCHAR(MAX)) AS NomeTab, CAST(CO.Nome AS VARCHAR(MAX)), 
               CO.Nivel, TC.Nome AS Tipo, CO.Status, CO.Nome AS apenasNome
        FROM Conta CO INNER JOIN TipoConta TC ON CO.IdTipoConta = TC.Id
        WHERE IdContaPai = 21720
          AND IdCliente = 1125
        UNION ALL
        SELECT C.ID, NivelRec + 1,
            CAST(REPLICATE('----', NivelRec) + C.NOME AS VARCHAR(MAX)) AS NomeTab,
            CAMINHO + ' \ ' + C.NOME,
            C.Nivel, TC.Nome AS Tipo,
            C.Status, C.Nome AS apenasNome
            FROM CONTA C
            INNER JOIN cteNiveis CTE ON C.IdContaPai = IdConta
            INNER JOIN TipoConta TC ON C.IdTipoConta = TC.Id 
    )
    SELECT IdConta, (CAST(Nivel as varchar(max)) + ' -' + NomeTab) AS Nivel, Caminho, Tipo, Status, Nome
      FROM cteNiveis 
      ORDER BY Caminho, Nome
    OPTION (MAXRECURSION 5)

The return is this, I hope you’ve managed to understand.

Thank you in advance to those who can help.

Note that the "Path" field of the table is like this:

Property tax "A, B or C" Lot "1, 10, 11, 12, 13, .... 2, 3, 4..."

I would like the letters "QUADRA" and the numbers of "LOTE" to be in the correct order (sequential)..

NÍVEL                       CAMIHO

1 - IPTU                   IPTU
2 -- Quadra A              IPTU \ Quadra A
3 --- Lote 1               IPTU \ Quadra A \ Lote 1
3 --- Lote "2"             IPTU \ Quadra A \ Lote "2"
3 --- Lote "3"             IPTU \ Quadra A \ Lote "3"
3 --- Lote "4"             IPTU \ Quadra A \ Lote "4"

And so on and so forth....

Answer 1

If the column path always has this pattern, you can manipulate the varchar to convert the final numbers into integer and so the ordering is correct, follow below an example and note the ORDER BY:

DECLARE @teste TABLE (
    nivel VARCHAR(MAX),
    caminho VARCHAR(MAX)
);

INSERT @teste VALUES
('1 - IPTU', 'IPTU'),
('2 -- Quadra A', 'IPTU \ Quadra A'),
('3 --- Lote 1', 'IPTU \ Quadra A \ Lote 1'),
('3 --- Lote 2', 'IPTU \ Quadra A \ Lote 2'),
('3 --- Lote 3', 'IPTU \ Quadra A \ Lote 3'),
('3 --- Lote 4 ', 'IPTU \ Quadra A \ Lote 4'),
('3 --- Lote 5 ', 'IPTU \ Quadra A \ Lote 5'),
('3 --- Lote 6 ', 'IPTU \ Quadra A \ Lote 6'),
('3 --- Lote 7 ', 'IPTU \ Quadra A \ Lote 7'),
('3 --- Lote 8 ', 'IPTU \ Quadra A \ Lote 8'),
('3 --- Lote 9 ', 'IPTU \ Quadra A \ Lote 9'),
('3 --- Lote 10 ', 'IPTU \ Quadra A \ Lote 10');

SELECT
    nivel,
    caminho,
    patindex('%[0-9]%', caminho),
    (CASE PATINDEX('%[0-9]%', caminho) WHEN 0 THEN caminho ELSE LEFT(caminho, PATINDEX('%[0-9]%', caminho) - 1) END),
    ISNULL(STUFF(caminho, 1, PATINDEX('%[0-9]%', caminho) - 1, ''), 0)
FROM
    @teste
ORDER BY
    (CASE PATINDEX('%[0-9]%', caminho) WHEN 0 THEN caminho ELSE LEFT(caminho, PATINDEX('%[0-9]%', caminho) - 1) END),
    CONVERT(INT, ISNULL(STUFF(caminho, 1, PATINDEX('%[0-9]%', caminho) - 1, ''), 0))