求一sql 语句，关于字符分割 问题
我有一个表 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 @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',',') --结果：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 @i=@i+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 @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 '结果: '+@conENDexec 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 @i=@i+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|*/
[解决办法]
- SQL code
create table taba(id int,num varchar(50))insert into tabaselect 1,'10001,10,1|10002,20,3|10003,50,8|' union allselect 2,'10041,10,1|10012,20,3|10002,50,8|'select * from tabaid num----------- --------------------------------------------------1 10001,10,1|10002,20,3|10003,50,8|2 10041,10,1|10012,20,3|10002,50,8|with t1 as(select a.id,substring(a.num,b.number,charindex('|',a.num+'|',b.number)-b.number) num1from taba ainner join master.dbo.spt_values bon b.[type]='P' and substring('|'+a.num,b.number,1)='|' and b.number<=len(a.num)),t2 as(select a.id,a.rn,row_number() over(partition by id,rn order by getdate()) rn2,substring(a.num1,b.number,charindex(',',a.num1+',',b.number)-b.number) num2from (select id,row_number() over(partition by id order by getdate()) rn, num1 from t1) ainner join master.dbo.spt_values bon b.[type]='P' and substring(','+a.num1,b.number,1)=','),t3 as(select id,rn,case rn2 when 1 then num2 when 2 then num2*5 when 3 then num2 end num2from t2),t4 as(select a.id,stuff((select ','+cast(num2 as varchar) from t3 t where t.id=a.id and t.rn=a.rn for xml path('')),1,1,'') num3from t3 agroup by a.id,a.rn)select c.id,cast((select num3+'|' from t4 d where d.id=c.id for xml path('')) as varchar(100)) numfrom t4 cgroup by c.idid num----------- --------------------------------------1 10001,50,1|10002,100,3|10003,250,8|2 10041,50,1|10012,100,3|10002,250,8|