--处理函数
create function f_str(@str varchar(8000))
returns varchar(8000)
as
begin
declare @t table(num int,st varchar(10))
declare @a int,@b varchar(10),@i int
select @str=replace(@str,',','')
,@i=patindex('%[^0-9]%',@str)
while @i>0
begin
select @a=left(@str,@i-1)
,@str=substring(@str,@i,8000)
,@i=patindex('%[0-9]%',@str)
,@b=case @i when 0 then @str else left(@str,@i-1) end
insert @t values(@a,@b)
if @i>0
select @str=substring(@str,@i,8000)
,@i=patindex('%[^0-9]%',@str)
end
set @str=''
select @str=@str+cast(num as varchar)+st
from(
select num=sum(num),st from @t group by st
)a order by st
return(@str)
end
go
--调用示例
select dbo.f_str('3a14b16c,14a5b9d'),dbo.f_str('3a14b16cccc,14a5b9d')
go
--处理函数
create function f_str(@str varchar(8000))
returns varchar(8000)
as
begin
declare @t table(num int,st varchar(10))
declare @a int,@b varchar(10),@i int
select @str=replace(@str,',','')
,@i=patindex('%[^0-9]%',@str)
while @i>0
begin
select @a=left(@str,@i-1)
,@str=substring(@str,@i,8000)
,@i=patindex('%[0-9]%',@str)
,@b=case @i when 0 then @str else left(@str,@i-1) end
insert @t values(@a,@b)
if @i>0
select @str=substring(@str,@i,8000)
,@i=patindex('%[^0-9]%',@str)
end
set @str=''
select @str=@str+cast(num as varchar)+st
from(
select num=sum(num),st from @t group by st
)a order by st
return(@str)
end
go