日期:2014-05-18 浏览次数:20647 次
create function f_splitstr(@SourceSql varchar(8000),@StrSeprate varchar(100)) returns @temp table(F1 varchar(100)) as begin declare @ch as varchar(100) set @SourceSql=@SourceSql+@StrSeprate while(@SourceSql<>'') begin set @ch=left(@SourceSql,charindex(',',@SourceSql,1)-1) insert @temp values(@ch) set @SourceSql=stuff(@SourceSql,1,charindex(',',@SourceSql,1),'') end return end ----调用 select * from dbo.f_splitstr('1,2,3,4',',') --结果: 1 2 3 4
------解决方案--------------------
create table taba(id int,num varchar(50)) insert into taba select 1,'1|2' union all select 2,'10|20' union all select 3,'3|4|5' select * from taba id num ----------- -------------------------------------------------- 1 1|2 2 10|20 3 3|4|5 with t as (select a.id, cast(substring(a.num,b.number,charindex('|',a.num+'|',b.number)-b.number) as int)*5 num2 from taba a inner join master.dbo.spt_values b on b.[type]='P' and substring('|'+a.num,b.number,1)='|' ) select t1.id, stuff(cast((select '|'+cast(num2 as varchar(10)) from t t2 where t2.id=t1.id for xml path('')) as varchar),1,1,'') num from t t1 group by t1.id id num ----------- ------------------------------ 1 5|10 2 50|100 3 15|20|25
------解决方案--------------------
/*创建一个函数*/ create function [dbo].[m_split_test](@c varchar(2000),@split varchar(2)) returns varchar(500) as begin declare @t table(col varchar(200)) while(charindex(@split,@c)<>0) begin insert @t(col) values (substring(@c,1,charindex(@split,@c)-1)) set @c = stuff(@c,1,charindex(@split,@c),'') end insert @t(col) values (@c) declare @i varchar(8000) set @i='' select @i=@i+ltrim(col*5)+'|' from @t return @i end go declare @表A table (num VARCHAR(20)) insert into @表A select '1|2|3' union all select '4|5|7' select [dbo].[m_split_test](num,'|') from @表A /* 5|10|15| 20|25|35| */
------解决方案--------------------
alter PROCEDURE proc_Update @param varchar(8000) AS BEGIN declare @temp int declare @con varchar(8000)='' print '开始: '+ @param set @param=@param+'|' while(@param<>'') begin set @temp=convert(int,left(@param,Charindex('|',@param,1)-1)) set @con = @con + convert(varchar(30),@temp*5) + '|' set @param=stuff(@param,1,charindex('|',@param,1),'') if (@param is null or @param='') begin set @con = SUBSTRING(@con,0,len(@con)) end end print '结果: '+@con END exec proc_Update @param='1|2|3' ----------------------------- 开始: 1|2|3 结果: 5|10|15
------解决方案--------------------
/*创建一个函数*/ alter function [dbo].[m_split_test](@c varchar(2000),@split varchar(2)) returns varchar(500) as begin declare @t table(col varchar(200)) while(charindex(@split,@c)<>0) begin insert @t(col) values (substring(@c,1,charindex(@split,@c)-1)) set @c = stuff(@c,1,charindex(@split,@c),'') end if(@c!=' ' and @c is not null and @c!='') begin insert @t(col) values (@c) end declare @i varchar(8000) set @i='' select @i=@i