create proc p (@s varchar(8000))
as
declare @re varchar(8000)
declare @tb table(col int)
set @s=@s+','
set @re=''
while charindex(',',@s)>0
begin
insert into @tb values(left(@s, charindex(',',@s)-1))
set @s=substring(@s,charindex(',',@s)+1,8000)
end
select @s=@s+','+cast(col as varchar) from @tb order by col
select stuff(@s,1,1,'')
go
exec p '12,56,45,87,123,44,50'
drop proc p
as
declare @re varchar(8000)
declare @tb table(col int)
set @s=@s+','
set @re=''
while charindex(',',@s)>0
begin
insert into @tb values(left(@s, charindex(',',@s)-1))
set @s=substring(@s,charindex(',',@s)+1,8000)
end
select @s=@s+','+cast(col as varchar) from @tb order by col
select stuff(@s,1,1,'')
go
exec p '12,56,45,87,123,44,50'
drop proc p
CREATE function splitstr(@SourceSql varchar(8000),@StrSeprate varchar(5))
returns @temp table(F1 INT)
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>'' insert @temp values(@SourceSql)
return
end--一条语句查出结果
select * from dbo.splitstr('12,44,45,50,56,87,123',',') order by F1 asc
功能:实现split功能的函数
*/create function fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as begin
declare @i int set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr) while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1)) set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end if @inputstr <> '\'
insert @temp values(@inputstr) return
end
go
--创建存储过程
create proc sp_test (@input varchar(1000),@output varchar(1000) output)
ascreate table #t (a int)insert into #t (a)
select a
from dbo.fn_split(@input,',')declare @s varchar(1000)set @s=''select @s=@s + ',' + cast(a as varchar(100)) from #t order by a
set @s=stuff(@s,1,1,'')set @output = @s
go
declare @a varchar(100),@b varchar(1000)set @a = '12,56,45,87,123,44,50'exec sp_test @a , @b outputselect @bdrop function dbo.fn_split
drop proc sp_test
/*返回:12,44,45,50,56,87,123*/
功能:实现split功能的函数
*/create function fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as begin
declare @i int set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr) while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1)) set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end if @inputstr <> '\'
insert @temp values(@inputstr) return
end
go--创建存储过程
create proc sp_test (@input varchar(1000),@output varchar(1000) output)
ascreate table #t (a int)insert into #t (a)
select a
from dbo.fn_split(@input,',')declare @s varchar(1000)set @s=''select @s=@s + ',' + cast(a as varchar(100)) from #t order by a
set @s=stuff(@s,1,1,'')set @output = @sdrop table #t
go--调用存储过程
declare @a varchar(100),@b varchar(1000)set @a = '12,56,45,87,123,44,50'exec sp_test @a , @b output--查询返回值
select @bdrop function dbo.fn_split
drop proc sp_test
/*返回:12,44,45,50,56,87,123*/
--关键代码
declare @i varchar(100)
set @i=''
select @i=@i+ cast (id as char(10))from t order by id
print @i12 44 45 50 56 87 123