declare @s varchar(10),@i int select @s='1+2+3+4',@i=0 select top 10 id=identity(int,1,1) into # from syscolumns select @i=@i+cast(substring(replace(@s,'+',''),id,1) as int) from # print @i drop table #--10
set nocount on create table test(col varchar(20)) insert test select '1+2+3+4' union all select '2+3+8+10' union all select '100+1+2+10' goselect top 100 id=identity(int,1,1) into tmp from syscolumns goalter function fun(@s varchar(20),@split varchar(10)) returns int as begin declare @i int set @i=0 select @i=@i+cast(substring(@s,id,charindex(@split,@s+@split,id)-id) as int) from tmp where id<=len(@s+'!') and charindex(@split,@split+@s,id)=id return @i end goselect 和=dbo.fun(col,'+') from testdrop table test,tmp和 ----------- 10 23 113
alter function fun改为create function fun
--创建测试表 create table test(col varchar(20)) insert test select '1+2+3+4' union all select '2+3+8+10' union all select '100+1+2+10' go--创建函数 create function f_getAdd( @s varchar(8000) --要分拆的字符串 ,@split varchar(10) --字符串分隔符 )returns int --返回和值新字段 as begin declare @table table(col int) declare @re int while charindex(@split,@s)>0 begin insert into @table values(left(@s,charindex(@split,@s)-1)) select @s=substring(@s,charindex(@split,@s)+len(@split),8000) end if @s<>'' insert into @table values(@s) select @re=0 select @re=@re+col from @table return(@re) end go--调用函数 select col,newField=dbo.f_getAdd(col,'+') from test go--删除表,删除函数 drop table test drop function dbo.f_getAdd go
--创建测试表 create table test(col varchar(20)) insert test select '1+2+3+4' union all select '2+3+8+10' union all select '100+1+2+10' go--创建函数 --方法一 create function f_getAdd( @s varchar(8000) --要分拆的字符串 ,@split varchar(10) --字符串分隔符 )returns int --返回和值新字段 as begin declare @table table(col int) declare @re int while charindex(@split,@s)>0 begin insert into @table values(left(@s,charindex(@split,@s)-1)) select @s=substring(@s,charindex(@split,@s)+len(@split),8000) end if @s<>'' insert into @table values(@s) select @re=0 select @re=@re+col from @table return(@re) end go--方法二 create function f_getAdd( @s varchar(8000) ,@split varchar(10) )returns int as begin declare @tb table(id int identity,col int) insert @tb select top 1000 0 from syscolumns a,syscolumns b declare @re int set @re=0 select @re=@re+substring(@s,id,charindex(@split,@s+@split,id)-id) from @tb a where id<=len(@s) and charindex(@split,@split+@s,id)=id return(@re) end--调用函数 select col,newField=dbo.f_getAdd(col,'+') from test go--删除表,删除函数 drop table test drop function dbo.f_getAdd go
select @s='1+2+3+4',@i=0
select top 10 id=identity(int,1,1) into # from syscolumns
select @i=@i+cast(substring(replace(@s,'+',''),id,1) as int) from #
print @i
drop table #--10
create table test(col varchar(20))
insert test select '1+2+3+4'
union all select '2+3+8+10'
union all select '100+1+2+10'
goselect top 100 id=identity(int,1,1) into tmp from syscolumns
goalter function fun(@s varchar(20),@split varchar(10))
returns int
as
begin
declare @i int
set @i=0 select @i=@i+cast(substring(@s,id,charindex(@split,@s+@split,id)-id) as int)
from tmp
where id<=len(@s+'!') and charindex(@split,@split+@s,id)=id return @i
end
goselect 和=dbo.fun(col,'+') from testdrop table test,tmp和
-----------
10
23
113
create table test(col varchar(20))
insert test select '1+2+3+4'
union all select '2+3+8+10'
union all select '100+1+2+10'
go--创建函数
create function f_getAdd(
@s varchar(8000) --要分拆的字符串
,@split varchar(10) --字符串分隔符
)returns int --返回和值新字段
as
begin
declare @table table(col int)
declare @re int
while charindex(@split,@s)>0
begin
insert into @table values(left(@s,charindex(@split,@s)-1))
select @s=substring(@s,charindex(@split,@s)+len(@split),8000)
end
if @s<>'' insert into @table values(@s)
select @re=0
select @re=@re+col from @table
return(@re)
end
go--调用函数
select col,newField=dbo.f_getAdd(col,'+') from test
go--删除表,删除函数
drop table test
drop function dbo.f_getAdd
go
create table test(col varchar(20))
insert test select '1+2+3+4'
union all select '2+3+8+10'
union all select '100+1+2+10'
go--创建函数
--方法一
create function f_getAdd(
@s varchar(8000) --要分拆的字符串
,@split varchar(10) --字符串分隔符
)returns int --返回和值新字段
as
begin
declare @table table(col int)
declare @re int
while charindex(@split,@s)>0
begin
insert into @table values(left(@s,charindex(@split,@s)-1))
select @s=substring(@s,charindex(@split,@s)+len(@split),8000)
end
if @s<>'' insert into @table values(@s)
select @re=0
select @re=@re+col from @table
return(@re)
end
go--方法二
create function f_getAdd(
@s varchar(8000)
,@split varchar(10)
)returns int
as
begin
declare @tb table(id int identity,col int)
insert @tb select top 1000 0 from syscolumns a,syscolumns b
declare @re int
set @re=0
select @re=@re+substring(@s,id,charindex(@split,@s+@split,id)-id)
from @tb a
where id<=len(@s)
and charindex(@split,@split+@s,id)=id
return(@re)
end--调用函数
select col,newField=dbo.f_getAdd(col,'+') from test
go--删除表,删除函数
drop table test
drop function dbo.f_getAdd
go