--有个现成的函数create function fn_num( @s varchar(2000) ) returns varchar(200) as begin declare @r varchar(200) set @r='' while PATINDEX('%[0-9]%',@s)>0 begin set @r=@r+substring(@s,PATINDEX('%[0-9]%',@s),1) set @s=stuff(@s,1,PATINDEX('%[0-9]%',@s),'') end return @r end go --调用 select dbo.fn_num('a2h5我j8 ') go
if object_id('fnDigit') is not null drop function fnDigit GO ----创建数组查找函数 create function fnDigit(@str varchar(1000)) returns bigint as begin declare @digit varchar(100) declare @i int set @i = 0 set @digit = '' while @i <> len(@str) begin set @i = @i + 1 if isnumeric(substring(@str,@i,1)) = 1 set @digit = @digit + substring(@str,@i,1) end return cast(@digit as bigint) end GO ----测试 select dbo.fnDigit('a2h5我j8')----清除测试环境 drop function fnDigit
returns varchar(200)
as
begin
declare @r varchar(200)
set @r=''
while PATINDEX('%[0-9]%',@s)>0 begin
set @r=@r+substring(@s,PATINDEX('%[0-9]%',@s),1)
set @s=stuff(@s,1,PATINDEX('%[0-9]%',@s),'')
end
return @r
end
go
--调用
select dbo.fn_num('a2h5我j8 ')
go
drop function fnDigit
GO
----创建数组查找函数
create function fnDigit(@str varchar(1000))
returns bigint
as
begin
declare @digit varchar(100)
declare @i int
set @i = 0
set @digit = ''
while @i <> len(@str)
begin
set @i = @i + 1
if isnumeric(substring(@str,@i,1)) = 1
set @digit = @digit + substring(@str,@i,1)
end
return cast(@digit as bigint)
end
GO
----测试
select dbo.fnDigit('a2h5我j8')----清除测试环境
drop function fnDigit