--创建处理函数
create function f_str(@str varchar(100))
returns varchar(1000)
as
begin
declare @r varchar(1000),@i
select @r='',@i=charindex('-',@str+'-')
while @i>0
select @r=@r+space(6-@i)+left(@str,@i)
,@str=substring(@str,@i+1)
,@i=charindex('-',@str+'-')
return(@r)
end
go--调用函数实现排序
select * from 表
order by dbo.f_str(a)
create function f_str(@str varchar(100))
returns varchar(1000)
as
begin
declare @r varchar(1000),@i
select @r='',@i=charindex('-',@str+'-')
while @i>0
select @r=@r+space(6-@i)+left(@str,@i)
,@str=substring(@str,@i+1)
,@i=charindex('-',@str+'-')
return(@r)
end
go--调用函数实现排序
select * from 表
order by dbo.f_str(a)
select '1','12'
union all select '2','12'
union all select '1-4','12'
union all select '11-55','12'
union all select '22-55','12'
union all select '1-1-','12'
union all select '2-58','12'select *,left(a,1),substring(a,2,1)
from t1 order by substring(a,2,1),left(a,1)a b
------------
1 12
2 12
1-4 12
1-1- 12
2-58 12
11-55 12
22-55 12
----------------------------
----------------------------
A B
01 XX
01-01 ###
03-04 ddd
02-01 jkd
01-02 djfkds
...
create function f_str(@str varchar(100))
returns varchar(1000)
as
begin
declare @r varchar(1000),@i int
select @r='',@i=charindex('-',@str+'-')
while @i>0
select @r=@r+space(6-@i)+left(@str,@i)
,@str=stuff(@str,1,@i,'')
,@i=charindex('-',@str+'-')
return(@r)
end
go--调用函数实现排序
select *
from 表
order by dbo.f_str(a)
create table 表(A varchar(10),B varchar(10))
insert 表 select '1','XX'
union all select '1-1','###'
union all select '3-4','ddd'
union all select '2-1','kd'
union all select '1-2','djfkds'
union all select '1-2-1','kd'
union all select '11-22','df'
union all select '41-2','kls'
union all select '1-2-1-1','fjkdl'
union all select '2-1-1','kdjkf'
union all select '2-3-1','djfksla'
go--创建处理函数
create function f_str(@str varchar(100))
returns varchar(1000)
as
begin
declare @r varchar(1000),@i int
select @r='',@i=charindex('-',@str+'-')
while @i>0
select @r=@r+space(6-@i)+left(@str,@i)
,@str=stuff(@str,1,@i,'')
,@i=charindex('-',@str+'-')
return(@r)
end
go--调用函数实现排序
select *
from 表
order by dbo.f_str(a)
go--删除测试
drop table 表
drop function f_str/*--测试结果A B
---------- ----------
1 XX
1-1 ###
1-2 djfkds
1-2-1 kd
1-2-1-1 fjkdl
2-1 kd
2-1-1 kdjkf
2-3-1 djfksla
3-4 ddd
11-22 df
41-2 kls(所影响的行数为 11 行)
--*/
insert @a
select
'1' , 'XX'
union all
select
'1-1' , '###'
union all
select
'3-4' , 'ddd'
union all
select
'2-1' , 'jkd'
union all
select
'1-2' , 'djfkds'
union all
select
'1-2-1' , 'jkd'
union all
select
'11-22' , 'df'
union all
select
'41-2' ,'kls'
union all
select
'1-2-1-1', 'fjkdl'
union all
select
'2-1-1' , 'jkdjkf'
union all
select
'2-3-1' , 'djfksla'select * from @a
order by right('000'+left(a,charindex('-',a+'-')-1),2)+right(a,len(a)-charindex('-',a+'-')+1)结果:
A B
---------- --------------------
1 XX
1-1 ###
1-2 djfkds
1-2-1 jkd
1-2-1-1 fjkdl
2-1 jkd
2-1-1 jkdjkf
2-3-1 djfksla
3-4 ddd
11-22 df
41-2 kls(所影响的行数为 11 行)