having issues sorting alpha numeric chars

Question

I am trying to sort one column which have alphanumeric letters

see my query below

SELECT d.number
FROM table name d, table_name 2 a WHERE d.case_id ='11-41'
AND d.ExhibitTypeId = TypeId AND d.ComplianceNo = '0' and
active = 1 and number is not null order by case
when ISNUMERIC(d.number) = 1 then right('0000000000'+d.number+'0',10)
else right('0000000000'+d.number,10)
end

This is the output

1
2
3
11
12
2A1

I want this output instead

1
2
2A1
3
11
12

Any help regarding this is greatly appreciated.

Answer 1

Assuming SQL Server this may work with some tweaks

SELECT
    d.number
FROM
    table name d,
    table_name 2 a
WHERE
    d.case_id ='11-41'
    AND
    d.ExhibitTypeId = TypeId
    AND
    d.ComplianceNo = '0'
    and
    active = 1
    and number is not null
order by
    Convert(int, LEFT(number, Case 
                            When PATINDEX('%[^0-9]%', number) > 0 Then PATINDEX('%[^0-9]%', number) - 1
                            Else LEN(number)
                        End)
    ),
    LEN(Number)

Answer 2

If(ISNUMERIC(LEFT(case,2)

BEGIN

order by case

END

else

BEGIN

order by LEFT(case,1), LEFT(case,2)

END