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

------解决方案--------------------
SQL code

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

------解决方案--------------------
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 
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|
*/

------解决方案--------------------
SQL code

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

------解决方案--------------------

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