Mysql statement for retrieving numbers from strings

Copy codeThe Code is as follows: one:
Declare @ s varchar (20)
Declare @ I varchar (20)
Set @ I =''
Set @ s = 'new members must purchase 350 yuan product'
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 'new members must purchase a product of 350 RMB'
Union all select 'the first order of the new store must be 20000 yuan'

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

On

Select substring (the queried string, patindex ('% [^ 0-9] [0-9] %', the queried string) + 1, patindex ('% [0-9] [^ 0-9] %', the queried string) -patindex ('% [^ 0-9] [0-9] %', the queried string) can only be used to query the number string that appears in the string for the first time.

So what if the appearance of the string sss89450ss http://www.jb51.net ss8989ss8989ss8989 7879aafds789 432432432543534 should be how to get it

InstanceCopy codeCode: create function fn_GetNum (@ s varchar (8000 ))
Returns varchar (8000)
As
Begin
Select @ s = 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 'aaa111_bbb'
Union all select 'ccc212ss'
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

/* Function: obtain letters in a string */
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
-- Test
Select dbo. F_Get_STR ('test abc123abc ')
GO
/*
Function: gets numbers in a string.
*/
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
-- Test
--- Select dbo. F_Get_Number ('test AB3C123AB5C ')
GO

In this way, no matter which combination you are, we can easily extract all the numbers in the characters.