34,590
社区成员
发帖
与我相关
我的任务
分享
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
(所影响的行数为 3 行)
*/
go
create table #s(
col varchar(10)
)
insert #s
select 'B3' union all
select 'C1' union all
select 'D\4' 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
*/
--必须在字符串最右边加一个‘空格’才可以使用
如果无规律,就用这个方法就可以了
declare @str varchar(100),@str2 varchar(100)
set @str= 'asdl;fkjqwopi-437y69t8ewasdlmbh495u95tofdms74547t '
select @str2=substring(@str,patindex( '%[0-9]% ',@str),len(@str)) --得到以第一个数字开始的字符串
,@str2=left(@str2,patindex( '%[^0-9]% ',@str2)-1) --得到第一个非数字开始的位置,并根据此位置删除其及以后的字符
print @str2 --显示结果