----------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-10-12 15:20:35
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--    Apr 22 2011 11:57:00 
--    Copyright (c) Microsoft Corporation
--    Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col] varchar(10))
insert [tb]
select '1-1-ache' union all
select '1-2-ache' union all
select '1-11-jjj' union all
select '1-23-jjll1' union all
select '1-3-ache'
--------------开始查询--------------------------

select * from [tb] order by PARSENAME(REPLACE(col,'-','.'),1),PARSENAME(REPLACE(col,'-','.'),2)
----------------结果----------------------------
/* col
----------
1-1-ache
1-2-ache
1-3-ache
1-11-jjj
1-23-jjll1

(5 行受影响)
*/

------解决方案--------------------
SQL code
select * from tb order by convert(int,left(col,charindex('-',col)-1)),
convert(int,substring(col,charindex('-',col)+1,charindex('-',col,charindex('-',col)+1)-charindex('-',col)-1))

------解决方案--------------------
SQL code
create table [tb]([col] varchar(10))
insert [tb]
select '1-1-ache' union all
select '1-2-ache' union all
select '1-11-jjj' union all
select '1-23-jjll1' union all
select '1-3-ache'
go
select * from tb order by convert(int,left(col,charindex('-',col)-1)),
convert(int,substring(col,charindex('-',col)+1,charindex('-',col,charindex('-',col)+1)-charindex('-',col)-1))
/*
col
----------
1-1-ache
1-2-ache
1-3-ache
1-11-jjj
1-23-jjll1

(5 行受影响)*/
go
drop table tb

------解决方案--------------------
SQL code

select * from tb
order by 
REPLACE(col,'#','')
--//结果
id          col
----------- ----------------------------------------------------------------
2           1#11#
53          1#11#4580#
7           1#11#4580#4581#
84          1#11#5175#
54          1#11#5175#5238#
8           1#11#5175#5238#5272#
85          1#11#5175#5547#
55          1#11#5175#5547#5565#
9           1#11#5175#5547#5565#5566#
10          1#11#5175#5547#5565#5572#
56          1#11#5175#5547#5573#
11          1#11#5175#5547#5573#5574#
57          1#11#5175#5547#5600#
12          1#11#5175#5547#5600#5601#
13          1#11#5175#5547#5600#5607#
3           1#17#
87          1#17#5843#
86          1#17#5843#5844#
58          1#17#5843#5844#5868#
14          1#17#5843#5844#5868#5869#
59          1#17#5843#5914#
15          1#17#5843#5914#5915#
4           1#21#
89          1#21#6871#
62          1#21#6871#6925#
18          1#21#6871#6925#6926#
90          1#21#6930#
64          1#21#6930#6931#
63          1#21#6930#6931#6937#
19          1#21#6930#6931#6937#6940#
20          1#21#6930#6931#6973#
91          1#21#6930#7065#
65          1#21#6930#7065#7066#
21          1#21#6930#7065#7066#7072#
22          1#21#6930#7065#7066#7082#
92          1#21#6930#7135#
66          1#21#6930#7135#7136#
23          1#21#6930#7135#7136#7137#
93          1#21#7176#
67          1#21#7176#7234#
24          1#21#7176#7234#7237#
69          1#21#7176#7320#
68          1#21#7176#7320#7321#
25          1#21#7176#7320#7321#7323#
26          1#21#7176#7320#7328#
70          1#21#7176#7320#7330#
27          1#21#7176#7320#7330#7331#
28          1#21#7176#7320#7345#
72          1#21#7176#7346#
71          1#21#7176#7346#7347#
29          1#21#7176#7346#7347#7353#
30          1#21#7176#7346#7366#
31          1#21#7176#7346#7368#
82          1