SQL order by complex string column as number

Question

I have a table like this:

| ID | Name | Code  |
---------------------
| 1  | test | 11-2  |
| 2  | test | 11/1  |
| 3  | test | 1     |
| 4  | test | 10x   |
| 5  | test | 11-11 |
| 6  | test | *     |
| 7  | test | 2     |
| 8  | test | m10   |
| 9  | test | 11-*  |
| 10 | test | 11    |
| 11 | test | 10    |

The Code column type is NVARCHAR. I want to get the records ordering by Code so that they sort like this:

*
1
2
10
10x
11
11-*
11/1
11-2
11-11
m10

But simple 'order by Code' query returns this:

*
1
10
10x
11
11-*
11/1
11-11
11-2
2
m10

Answer 1

try this

select *,ISNUMERIC(LEFT(t,1)) as num,REPLICATE('0',10-len(t))+RTRIM(t) as ord
from(
select '11-2 ' as t union all
select '11/1 ' union all
select '1    ' union all
select '10x  ' union all
select '11-11' union all
select '*    ' union all
select '2    ' union all
select 'm10  ' union all
select '11-* ' union all
select '11   ' union all
select '10   ' 
)as p

order by num desc,ord