日期:2014-05-17 浏览次数:20635 次
if object_id('dbo.f_str') is not null
drop function dbo.f_str
go
create function dbo.f_str(@a varchar(10))
returns int
as
begin
declare @cnt as int
set @cnt = 0
declare @i as int
declare @j as int
declare @k1 as int
declare @k2 as int
set @i = 1
set @j = len(@a)
set @k1 = 0
set @k2 = 0
while @i <= @j
begin
if substring(@a , @i , 1) between '0' and '9'
begin
if @k1 = 0
set @k1 = @i
if @i = @j
begin
set @k2 = @j
set @cnt = @cnt + cast(substring(@a , @k1 , (@k2 - @k1 + 1)) as int)
end
end
else
begin
if @k1 > 0
begin
set @k2 = @i - 1
set @cnt = @cnt + cast(substring(@a , @k1 , (@k2 - @k1 + 1)) as int)
end
set @k1 = 0
set @k2 = 0 end
set @i = @i + 1
end
return @cnt
end
go
--调用函数
select [sum] = dbo.f_str('王武:13%') + dbo.f_str('张三:14%') + dbo.f_str('李四:16%')
/*
sum
-------
43
*/
select CONVERT(int,Substring('王武:13%',CHARINDEX(':','王武:13%')+1,CHARINDEX('%','王武:13%')-CHARINDEX(':','王武:13%')-1))
+ Substring('张三:14%',CHARINDEX(':','张三:14%')+1,CHARINDEX('%','张三:14%')-CHARINDEX(':','张三:14%')-1)
+ Substring('李四:16%',CHARINDEX(':','李四:16%')+1,CHARINDEX('%','李四:16%')-CHARINDEX(':','李四:16%')-1)