select * from tb order by cast ( left([col1],patindex('%[^0-9]%',[col1]+',')-1 ) as int)
,[col1] desc
结果:
col1
zz
dd
aa
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select left([col1],patindex('%[^0-9]%',[col1]+',')-1 ) from tb
过滤掉了字符的.
select * from tb order by cast ( left([col1],patindex('%[^0-9]%',[col1]+',')-1 ) as int)
,[col1] desc
结果:
col1
zz
dd
aa
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select left([col1],patindex('%[^0-9]%',[col1]+',')-1 ) from tb
过滤掉了字符的.