SQL Server中提取字符串中数字的最大值
比如:字符串“ 15%、16.5%，25%、29%、30%，中国20.5 "
如何提取里面的数字的最大值呢?
得到结果30.
在线等
[解决办法]
-- 建函数
create function dbo.fn_getmaxnum
(@x varchar(1000)) returns varchar(10)
as
begin
declare @i int
declare @s varchar(100)
declare @maxnum decimal(10,1)
select @i=1,@s=''
while(@i<=len(@x))
begin
if (ascii(substring(@x,@i,1)) in(48,49,50,51,52,53,54,55,56,57,46,37))
begin
select @s=@s+substring(@x,@i,1)
end
select @i=@i+1
end
select @maxnum=max(cast(substring(a.s,
b.number,
charindex('%',a.s+'%',b.number)-b.number) as decimal(10,1)))
from (select @s 's') a,master.dbo.spt_values b
where b.type='P' and b.number between 1 and len(a.s) and substring('%'+a.s,b.number,1)='%'
return replace(cast(@maxnum as varchar(10)),'.0','')
end
-- 测试1
declare @x varchar(1000)
select @x=' 15%、16.5%，25%、29%、30%，中国20.5 '
select dbo.fn_getmaxnum(@x) '数字最大值'
/*
数字最大值
----------
30
(1 row(s) affected)
*/
-- 测试2
declare @x varchar(1000)
select @x=' 15%、16.5%，25%、29%、30%，中国350.5 '
select dbo.fn_getmaxnum(@x) '数字最大值'
/*
数字最大值
----------
350.5
(1 row(s) affected)
*/
-- 测试3
declare @x varchar(1000)
select @x=' 15%、16.5%，32%、29%、30%，中国20.5 '
select dbo.fn_getmaxnum(@x) '数字最大值'
/*
数字最大值
----------
32
(1 row(s) affected)
*/
--建立函数
create function dbo.get_max(@v nvarchar(1000))
returns int
as
begin
declare @i int
declare @return int
declare @number nvarchar(20)
declare @t table(v nvarchar(20))
set @v= @v+'x'
set @i = 1
set @number = ''
while @v <> ''
begin
if LEFT(@v,1) like '%[0-9]%' or LEFT(@v,1) like '%.%'
set @number = @number + LEFT(@v,1)
else
begin
insert @t
select @number
set @number = ''
end
set @v = stuff(@v,1,1,'')
end
select @return = MAX(v)
from @t
return @return
end
go
select dbo.get_max('15%、16.5%，25%、29%、30%，中国20.5')
/*
30
*/
--建立函数
create function dbo.get_max(@v nvarchar(1000))
returns int
as
begin
declare @i int
declare @return int
declare @number nvarchar(20)
declare @t table(v nvarchar(20))
set @v= @v+'x'
set @i = 1
set @number = ''
while @v <> ''
begin
if LEFT(@v,1) like '%[0-9]%' or LEFT(@v,1) like '%.%'
set @number = @number + LEFT(@v,1)
else
begin
insert @t
select @number
set @number = ''
end
set @v = stuff(@v,1,1,'')
end
select @return = MAX(cast(v as float))
from @t
return @return
end
go
select dbo.get_max('15%、16.5%，25%、29%、30%，中国20.5,100中国89每个')
/*
100
*/
declare @str1 varchar(500)
set @str1='15%、16.5%，25%、29%、30%，中國20.5'
select max(sz)
from
(
select b.gr,sz= (select ''+sz from(
select sz= SUBSTRING (@str1 ,number ,1),gr=number-ROW_NUMBER () over(order by getdate()) from master .dbo.spt_values
where type='P' and number >0
and ISNUMERIC (SUBSTRING (@str1 ,number ,1 ))>0)a where a.gr =b.gr for XML path('')) from
(select gr from(
select sz= SUBSTRING (@str1 ,number ,1),gr=number-ROW_NUMBER () over(order by getdate()) from master .dbo.spt_values
where type='P' and number >0
and ISNUMERIC (SUBSTRING (@str1 ,number ,1 ))>0)a
group by gr) b
)c
--創建函數
CREATE FUNCTION dbo.get_max ( @str VARCHAR(MAX) )
RETURNS FLOAT
AS
BEGIN
DECLARE @tmp VARCHAR(101) ,
@i INT ,
@f FLOAT
SET @tmp = @str + 'a '
SET @f = 0
WHILE PATINDEX('%[0-9.]% ', @tmp) > 0
BEGIN
SET @i = 1
WHILE 1 = 1
BEGIN
IF ISNUMERIC(SUBSTRING(@tmp,
PATINDEX('%[0-9]% ', @tmp) + @i,
1)) = 0
BREAK
SET @i = @i + 1
END
IF CAST(SUBSTRING(@tmp, PATINDEX('%[0-9]% ', @tmp), @i) AS FLOAT) > @f
SET @f = CAST(SUBSTRING(@tmp, PATINDEX('%[0-9]% ', @tmp),
@i) AS FLOAT)
SET @tmp = STUFF(@tmp, PATINDEX('%[0-9]% ', @tmp), @i, ' ')
END
RETURN @f
END
--執行函數
SELECT dbo.get_max('15%、16.5%，25%、29%、30%，中国20.5')
