I like to do the AlphaNumeric sorting by using the sql select query in sql server 2005

10A
10C
11
12A
17A
1A
2A
4A
.
.
.
etc

I need this list to be as to

1A
2A
4A
10A
10C
11
12A
17A
.
.
.
etc 

For that I need to Split the field in to Numbeic and Alphabet and orderby Alphabet Field, Numeric Field

Alphabet Field = "RIGHT(C.CODE,PATINDEX('%[A-Z]%',REVERSE(C.CODE)))"
Numeric Field = "LEFT(C.CODE,LEN(C.CODE) - PATINDEX('%[0-9]%',C.CODE))"

select
 DISTINCT
 C.CODE AS TXT,
 C.CODE,
 CASE WHEN ISNUMERIC(C.CODE) = 1 THEN CAST(C.CODE AS INT) ELSE CAST(LEFT(C.CODE,LEN(C.CODE) - PATINDEX('%[0-9]%',C.CODE)) AS INT) END AS NumField ,
 CASE WHEN ISNUMERIC(C.CODE) = 0 THEN RIGHT(C.CODE,PATINDEX('%[A-Z]%',REVERSE(C.CODE))) ELSE C.CODE END AS AlphaField
FROM CATEGORY AS C
WHERE (C.CAT_TYPE = 'TRAN' OR C.CAT_TYPE = 'MEAL') AND (C.IN_USE = 'Y')
ORDER BY NumField, AlphaField, CODE