declare @t table(str varchar(20)) insert into @t select '5214.73.152' insert into @t select '5214.363.68'select * from @t order by cast(PARSENAME(str,3) as int),cast(PARSENAME(str,2) as int),cast(PARSENAME(str,1) as int)/* str ---------------- 5214.73.152 5214.363.68 */
--生成测试数据 declare @t table(str varchar(20)) insert into @t select '5214.73.152' insert into @t select '5214.363.68' go--创建用户定义函数 create function f_str(@str varchar(100)) returns varchar(400) as begin declare @ret varchar(200) set @ret = '' while(charindex('.',@str)>0) begin set @ret = @ret + right('000000'+left(@str,charindex('.',@str)-1),6) set @str = stuff(@str,1,charindex('.',@str),'') end set @ret = @ret + right('000000'+@str,6) return @ret end go--执行查询,调用函数排序 select * from @t order by dbo.f_str(str) go--删除用户定义函数 drop function f_str
insert into @t select '5214.73.152'
insert into @t select '5214.363.68'select * from @t
order by cast(PARSENAME(str,3) as int),cast(PARSENAME(str,2) as int),cast(PARSENAME(str,1) as int)/*
str
----------------
5214.73.152
5214.363.68
*/
declare @t table(str varchar(20))
insert into @t select '5214.73.152'
insert into @t select '5214.363.68'
go--创建用户定义函数
create function f_str(@str varchar(100))
returns varchar(400)
as
begin
declare @ret varchar(200)
set @ret = ''
while(charindex('.',@str)>0)
begin
set @ret = @ret + right('000000'+left(@str,charindex('.',@str)-1),6)
set @str = stuff(@str,1,charindex('.',@str),'')
end
set @ret = @ret + right('000000'+@str,6)
return @ret
end
go--执行查询,调用函数排序
select * from @t order by dbo.f_str(str)
go--删除用户定义函数
drop function f_str