create table A(a1 varchar(10),a2 varchar(10),a3 varchar(10))
insert into a values('B3','C1','D\4')
insert into a values('B31d','C12','D1\4')
insert into a values('B31ds2','C13','D2\4')
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(a1) + dbo.f_str(a2) + dbo.f_str(a3) from a

drop function dbo.f_str

drop table a

/*
a1         a2         a3         sum         
---------- ---------- ---------- ----------- 
B3         C1         D\4        8
B31d       C12        D1\4       48
B31ds2     C13        D2\4       52

（所影响的行数为 3 行）
*/

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

SQL code

go
create table #s(
col varchar(10)
)
insert #s
select 'B3' union all
select 'C1' union all
select 'D\4' union all
select '4MD' union all
select 'AR/12BD'
;with t
as(
select substring(col+' ',patindex( '%[0-9]% ',col+' '),len(col+' ')) as col1 
from #s
)
select left(col1+' ',patindex( '%[^0-9]% ',col1+' ')-1) as col from t
/*
col
3
1
4
12
4
*/
--加了一种情况,也可以的