declare @str varchar(100),@str2 varchar(100) set @str= 'B3 ' select @str2=substring(@str,patindex( '%[0-9]% ',@str),len(@str)) --得到以第一个数字开始的字符串 ,@str2=left(@str2,patindex( '%[^0-9]% ',@str2)-1)--得到第一个非数字开始的位置,并根据此位置删除其及以后的字符 select @str2 --显示结果 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 */ 方法二:创建处理函数:
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 */