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

Question

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

Navigation

#1 by (1 votes)

1

I have the following query below and would like to sort by image, but the number.

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 have managed to understand.

Thanksinadvancetoanyonewhocanhelp.

Notethatthe"Path" field of the table looks like this:

1, 10, 11, 12, 13, .... 2, 3, 4 ... "

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

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

sql-server query

asked by anonymous 06.04.2018 / 15:25

1 answer

How to receive the select value in PHP when selecting an option Views and session variables available in sub domains

score 1 · Answer 1

If the path column always has this pattern, you can manipulate varchar to convert the final numbers to integer and so the ordering is correct, follow an example below 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))