A statement in mysql that takes a number from a string
- 2020-05-13 03:37:02
- OfStack
one:
declare @s varchar(20)
declare @i varchar(20)
set @i=''
set @s=' New members must buy 350 Yuan products '
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 buy 350 Yuan products '
union all select ' A new store must be full on its first order 20000 yuan '
select left(right(a,len(a)-patindex('%[0-9]%',a)+1),len(right(a,len(a)-patindex('%[0-9]%',a)+1))-1) from @a
In the
select substring (the query string, patindex (' % [^ 0-9] [0-9] % ', the query string) + 1, patindex (' % [0-9] [^ 0-9] % ', the query string) - patindex (' % [^ 0-9] [0-9] % ', the query string)) this can only query string first appeared in a string of Numbers
So what if a string sss8989sss / / www ofstack. com ss8989ss8989ss8989 7879 aafds789 432432432543534 how should take
The instance
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
/* function : Gets the letters in the 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 the number in the 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
And then we can easily get all the Numbers out of the characters, no matter what combination you're in.