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人份' Gocreate 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 */
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人份'
Gocreate 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
*/