34,589
社区成员
发帖
与我相关
我的任务
分享
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#') is null
drop table #
Go
Create table #([Col1] nvarchar(100))
Insert #
select N'37360支、36840支' union all
select N'37440支、36440支' union all
select N'37750支' union all
select N'76325人份'
Go
create function F_num(@s nvarchar(100))
returns int
as
begin
declare @i int,@j int
set @i=0
while patindex('%[0-9]%',@s)>0
select @j=patindex('%[^0-9]%',@s),@i=@i+left(@s,@j-1),@s=stuff(@s,1,@j,''),@s=stuff(@s,1,patindex('%[0-9]%',@s)-1,'')
return @i
end
go
select dbo.F_num([Col1]) as COl1 from #
/*
74200
73880
37750
76325
*/