create function getorder(@field varchar(1000))
returns numeric(38)
as
begin
declare @bb int,@cc int,@value varchar(8000)
select @bb=0,@cc=charindex('.',@field)
set @value=right(replicate('0',3)+substring(@field,@bb,@cc-@bb),3)
if @value='' return left(@field+replicate('0',38),38)
while @cc>0
begin
set @bb=@cc+1
set @cc=charindex('-',@field,@bb)
set @value=@value+right(replicate('0',3)+cast(substring(@field,@bb,case when @cc>0 then @cc-@bb else len(@field) end) as varchar),3)
end
return cast(left(@value+replicate('0',38),38) as numeric(38))
end--测试:
declare @ table (a varchar(100))
insert @ values('1')
insert @ values('1.1')
insert @ values('2.1.44.1.2.34')
insert @ values('2.1.45.1.1')
insert @ values('2.1')
insert @ values('3.2')select * from @ order by dbo.getorder(a)
returns numeric(38)
as
begin
declare @bb int,@cc int,@value varchar(8000)
select @bb=0,@cc=charindex('.',@field)
set @value=right(replicate('0',3)+substring(@field,@bb,@cc-@bb),3)
if @value='' return left(@field+replicate('0',38),38)
while @cc>0
begin
set @bb=@cc+1
set @cc=charindex('-',@field,@bb)
set @value=@value+right(replicate('0',3)+cast(substring(@field,@bb,case when @cc>0 then @cc-@bb else len(@field) end) as varchar),3)
end
return cast(left(@value+replicate('0',38),38) as numeric(38))
end--测试:
declare @ table (a varchar(100))
insert @ values('1')
insert @ values('1.1')
insert @ values('2.1.44.1.2.34')
insert @ values('2.1.45.1.1')
insert @ values('2.1')
insert @ values('3.2')select * from @ order by dbo.getorder(a)
returns numeric(38)这句是什么意思?
insert @a values ('1.1')
insert @a values ('1.1.1')
insert @a values ('1.1.10')
insert @a values ('1.1.2')select * from @a order by cast(replace(a,'.','0') as numeric(38,0))
drop function getorder
create function getorder(@field varchar(1000))
returns numeric(38)
as
begin
declare @bb int,@cc int,@value varchar(8000)
set @field = @field +'.' //。。
select @bb=0,@cc=charindex('.',@field)
set @value=right(replicate('0',3)+substring(@field,@bb,@cc-@bb),3)
if @value='' return left(@field+replicate('0',38),38)
while @cc>0
begin
set @bb=@cc+1
set @cc=charindex('.',@field,@bb) //。。
set @value=@value+right(replicate('0',3)+cast(substring(@field,@bb,case when @cc>0 then @cc-@bb else len(@field) end) as varchar),3)
end
return cast(left(@value+replicate('0',38),38) as numeric(38))
end--测试:
declare @ table (a varchar(100))
insert @ values('1')
insert @ values('2')
insert @ values('1.1')
insert @ values('2.1.44.1.2.34')
insert @ values('2.1.45.1.1')
insert @ values('2.1')
insert @ values('3.2')select * from @ order by dbo.getorder(a)
drop function getorder
create function getorder(@field varchar(1000))
returns numeric(38)
as
begin
declare @bb int,@cc int,@value varchar(8000)
set @field = @field +'.' //。。
select @bb=0,@cc=charindex('.',@field)
set @value=right(replicate('0',3)+substring(@field,@bb,@cc-@bb),3)
if @value='' return left(@field+replicate('0',38),38)
while @cc>0
begin
set @bb=@cc+1
set @cc=charindex('.',@field,@bb) //。。
set @value=@value+right(replicate('0',3)+cast(substring(@field,@bb,case when @cc>0 then @cc-@bb else len(@field) end) as varchar),3)
end
return cast(left(@value+replicate('0',38),38) as numeric(38))
end--测试:
declare @ table (a varchar(100))
insert @ values('1')
insert @ values('2')
insert @ values('1.1')
insert @ values('2.1.44.1.2.34')
insert @ values('2.1.45.1.1')
insert @ values('2.1')
insert @ values('3.2')select * from @ order by dbo.getorder(a)
你的结果是这样的: 我要的是这样的:
1 1
2 1.1
1.1 1.1.1
1.2 1.1.10
2.1 1.2
2.2 2
1.1.1 2.1
1.1.10 2.2
.... ....