我有一个表 A
里面有个字段是num，是由很多数字用|分割的，现在我县分割，然后全部乘5，再重新连接。不知道怎么写，
------解决方案--------------------
看看这个吧，有你想要的
------解决方案--------------------
还是写个吧
- SQL code
create function f_splitstr(@SourceSql varchar(8000),@StrSeprate varchar(100)) returns @temp table(F1 varchar(100)) as begin declare @ch as varchar(100) set @[email protected][email protected] 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',',') --结果：1234
------解决方案--------------------
- SQL code
create table taba(id int,num varchar(50))insert into tabaselect 1,'1|2' union allselect 2,'10|20' union allselect 3,'3|4|5'select * from tabaid num----------- --------------------------------------------------1 1|22 10|203 3|4|5with t as(select a.id,cast(substring(a.num,b.number,charindex('|',a.num+'|',b.number)-b.number) as int)*5 num2from taba ainner join master.dbo.spt_values bon 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,'') numfrom t t1group by t1.idid num----------- ------------------------------1 5|102 50|1003 15|20|25
------解决方案--------------------
- SQL code
/*创建一个函数*/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 @[email protected]+ltrim(col*5)+'|' from @t return @i endgodeclare @表A table (num VARCHAR(20))insert into @表Aselect '1|2|3' union allselect '4|5|7'select [dbo].[m_split_test](num,'|') from @表A/*5|10|15|20|25|35|*/
------解决方案--------------------
- SQL code
alter PROCEDURE proc_Update @param varchar(8000)ASBEGIN declare @temp int declare @con varchar(8000)='' print '开始: '+ @param set @[email protected]+'|' 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 '结果: [email protected]ENDexec proc_Update @param='1|2|3'-----------------------------开始: 1|2|3结果: 5|10|15
------解决方案--------------------
- SQL code
/*创建一个函数*/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 @[email protected]+ltrim(parsename(replace(col,',','.'),3))+','+ltrim(5*parsename(replace(col,',','.'),2)) +','+ltrim(parsename(replace(col,',','.'),1)) +'|' from @t return @i enddeclare @T table(col varchar(200))insert into @Tselect '10001,2,1|10002,4,3|10003,10,8|' union allselect '10041,2,1|10012,4,3|10002,10,8|' union allselect '10041,2,1|10012,4,3|'select [dbo].[m_split_test](col,'|') from @T/*10001,10,1|10002,20,3|10003,50,8|10041,10,1|10012,20,3|10002,50,8|10041,10,1|10012,20,3|*/