--建立函数create function f_SortIntStr(@str varchar(3000))
returns varchar(3000)
as
begin
declare @r varchar(3000)
declare @t Table (a int)
declare @s varchar(3000)
declare @s1 varchar(3000)
set @r=@str+','
set @s=left(@r,1)
set @s1=''
while @r<>''
begin
if @s<>','
set @s1=@s1+@s
else
begin
insert @t values(@s1)
set @s1=''
end
set @r=right(@r,len(@r)-1)
set @s=left(@r,1)
end
set @r=''
select @r=@r+cast(a as varchar(10))+',' from @t order by a
set @r=left(@r,len(@r)-1)
return @r
end--调用
select dbo.f_SortIntStr('1,2,23,4,5,98,43')--结果1,2,4,5,23,43,98(所影响的行数为 1 行)
returns varchar(3000)
as
begin
declare @r varchar(3000)
declare @t Table (a int)
declare @s varchar(3000)
declare @s1 varchar(3000)
set @r=@str+','
set @s=left(@r,1)
set @s1=''
while @r<>''
begin
if @s<>','
set @s1=@s1+@s
else
begin
insert @t values(@s1)
set @s1=''
end
set @r=right(@r,len(@r)-1)
set @s=left(@r,1)
end
set @r=''
select @r=@r+cast(a as varchar(10))+',' from @t order by a
set @r=left(@r,len(@r)-1)
return @r
end--调用
select dbo.f_SortIntStr('1,2,23,4,5,98,43')--结果1,2,4,5,23,43,98(所影响的行数为 1 行)
select top 8000 id=identity(int,1,1) into 序数表
from syscolumns a,syscolumns b
alter table 序数表 add constraint pk_id_序数表 primary key(id)
go--处理函数
create function f_sort(
@str varchar(8000) --要排序的字符串
)returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+','+cast(st as varchar)
from(
select top 100 percent st=cast(substring(@str,id,charindex(',',@str+',',id)-id) as int)
from 序数表
where id<=len(@str)+1 and charindex(',',','+@str,id)-id=0
)a order by st
return(substring(@re,2,8000))
end
go--调用
select dbo.f_sort('1,2,23,4,11,5,98,43')