Existe uma maneira de classificar por uma coluna alfanumérica? [duplicado]

Para os valores de exemplo fornecidos:

SELECT 
    U.unitname
FROM dbo.units AS U
ORDER BY
    COALESCE
    (
        TRY_CONVERT(integer, SUBSTRING(U.unitname, 6, 2)),
        TRY_CONVERT(integer, SUBSTRING(U.unitname, 6, 1))
    ),
    RIGHT(U.unitname, 1);

A ideia é classificar pelo número máximo de caracteres que podem ser convertidos com sucesso em um número inteiro, desfazendo os empates com o caractere final.

Como alternativa, essa ideia insere zeros de prefixo suficientes para que a classificação de caracteres funcione conforme o esperado:

SELECT 
    U.unitname
FROM dbo.units AS U
ORDER BY
    STUFF
    (
        U.unitname, 
        PATINDEX('%[0123456789]%', U.unitname),
        0, 
        REPLICATE
        (
            '0', 
            -- varchar(20) less the length of the existing text
            20 - DATALENGTH(U.unitname) +
            -- Plus extra zeros for trailing non-numeric characters
            PATINDEX('%[0123456789]%', REVERSE(U.unitname))
        )
    );

Ambos retornam:

demonstração on-line db<>fiddle

Um design de tabela melhor pode ser:

CREATE TABLE dbo.Unit
(
    UnitNo tinyint NOT NULL
        CONSTRAINT [CK dbo.Unit UnitNo positive]
            CHECK (UnitNo > 0),
    UnitSuffix char(1) COLLATE Latin1_General_BIN2 NOT NULL
        CONSTRAINT [CK dbo.Unit Suffix blank or a-z]
            CHECK (UnitSuffix LIKE '[ a-z]'),

    CONSTRAINT [PK dbo.Unit UnitNo, UnitSuffix]
        PRIMARY KEY CLUSTERED (UnitNo, UnitSuffix)
);

INSERT dbo.Unit
    (UnitNo, UnitSuffix)
VALUES
    (1, ''),
    (2, 'b'),
    (2, 'c'),
    (2, 'a'),
    (3, 'a'),
    (3, 'c'),
    (10, ''),
    (11, '');

SELECT
    unitname = CONCAT('unit ', U.UnitNo, U.UnitSuffix)
FROM dbo.Unit AS U 
ORDER BY 
    U.UnitNo, 
    U.UnitSuffix;

Eu tentei a seguinte abordagem

select unitname
from units
cross apply(values(stuff(unitname,1,5,''))) s(u)
cross apply(values(cast(case when isnumeric(u) = 1 
then u else left(u, len(u) - 1) end as int))) s1(num)
cross apply(values(case when isnumeric(u) = 1 
then '' else right(u,1) end)) s2(alp)
order by num,alp

Embora isso funcione no meu caso, alguém pode sugerir uma abordagem melhor?

Eu faria algo assim (escrevi em pseudo-código para fins de tempo):

• Remova a primeira palavra "unidade" (opcional, mas facilita as coisas)
• Substitua "1" por "1_" (usando "len")
• classificar caracteres não numéricos primeiro (1_, 2a, 2b, 3a, etc, usando "case")
• classificar caracteres numéricos em segundo lugar (10, 11, etc, usando "else" para o caso anterior).