table A中有很多字段，比如a1、a2、a3等
三个字段中有字母和数字，比如：B3、C1、D\4等
现在要求各字段的数字和(排除字母和字符)，我现在想到的一个方法是
把replace函数写成自定义的一个函数在函数里面去逐个替换(可以当字符是固定的几个)，如：
sum(replace(replace(replace(replace(a1,'B',''),'C',''),'D',''),'\',''))
不知道还有没有更好的办法？
------解决方案--------------------
我用函数帮你实现,看是否合适?
- SQL code
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')gocreate function dbo.f_str(@a varchar(10)) returns intasbegin 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 @cntendgo--调用函数select * , [sum] = dbo.f_str(a1) + dbo.f_str(a2) + dbo.f_str(a3) from adrop function dbo.f_strdrop table a/*a1 a2 a3 sum ---------- ---------- ---------- ----------- B3 C1 D\4 8B31d C12 D1\4 48B31ds2 C13 D2\4 52（所影响的行数为 3 行）*/
------解决方案--------------------
- SQL code
gocreate table #s(col varchar(10))insert #sselect 'B3' union allselect 'C1' union allselect 'D\4' union allselect '4MD' union allselect 'AR/12BD';with tas(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/*col314124*/--加了一种情况,也可以的