mysql中取字符串中的数字的语句

one:
declare @s varchar(20)
declare @i varchar(20)
set @i=
set @s=新会员必须购买350元产品
while PATINDEX (%[0-9]%, @s)>0
begin
set @i=@i+substring(@s,PATINDEX (%[0-9]%, @s),1)
set @s=stuff(@s,1,PATINDEX (%[0-9]%, @s),)
end
select @i
--
300
two:
declare @a table(id int identity(1,1),a varchar(100))
insert @a select 新会员必须购买350元产品
union all select 新店首次定货必须满20000元

select left(right(a,len(a)-patindex(%[0-9]%,a)+1),len(right(a,len(a)-patindex(%[0-9]%,a)+1))-1) from @a

create function fn_GetNum(@s varchar(8000))
returns varchar(8000)
as
begin
select @s = stuff(stuff(@s, 1, patindex(%[0-9, .]%, @s) - 1, ),
patindex(%[^0-9, .]%, stuff(@s, 1, patindex(%[0-9, .]%, @s) - 1, )),
len(@s), )
return @s
end

declare @t table(s varchar(8000))
insert @t select aaa11112bbb
union all select ccc212sss
union all select sss21a
select dbo.fn_GetNum(s) as result from @t

select substring(s,patindex(%[^0-9][0-9]%,s)+1,patindex(%[0-9][^0-9]%,s)-patindex(%[^0-9][0-9]%,s)) from @t

/*功能:获取字符串中的字母*/
CREATE FUNCTION dbo.F_Get_STR (@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX(%[^a-z]%,@S)>0
BEGIN
set @s=stuff(@s,patindex(%[^a-z]%,@s),1,)
END
RETURN @S
END
GO
--测试
select dbo.F_Get_STR(测试ABC123ABC)
GO
/*
功能:获取字符串中的数字
*/
create function dbo.F_Get_Number (@S varchar(100))
returns int
AS
begin
while PATINDEX(%[^0-9]%,@S)>0
begin
set @s=stuff(@s,patindex(%[^0-9]%,@s),1,)
end
return cast(@S as int)
end
--测试
---select dbo.F_Get_Number(测试AB3C123AB5C)
GO