SQL Server中提取字符串中数字的最大值

比如:字符串“ 15%、16.5%，25%、29%、30%，中国20.5 "
如何提取里面的数字的最大值呢?
得到结果30.

在线等

16 个解决方案

#1

还有中国20.5
没规律,没法写.
只能写个遍历字符转换方法,把数字取出来

#2

-- 建函数
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)
*/

#3

这情况太多了，例如还有的是“100中国89每个”

#4

--建立函数
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
*/

#5

修改一下：

--建立函数
alter 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')
/*
30
*/

#6

--建立函数
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
*/

#7

引用 2 楼 ap0405140 的回复:

-- 建函数
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)
*/

谢谢斑竹，斑竹是万能的。

#8

declare @x varchar(1000)
select @x='5%，12.5%，15%，16.5,40.6%'
select dbo.fn_getmaxnum(@x) '数字最大值'


这样的就不行

#9

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

#10

最后一个串是中国20.5%？还是%也是属于字符串，并不表示百分号？

#11

引用 8 楼 hugofgh 的回复:

declare @x varchar(1000)
select @x='5%，12.5%，15%，16.5,40.6%'
select dbo.fn_getmaxnum(@x) '数字最大值'


这样的就不行

-- 建函数
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,163,44))
  begin
   select @s=@s+case when substring(@x,@i,1) in('，',',','、') and right(@s,1)='%' 
                       then '' 
                     when substring(@x,@i,1) in('，',',','、')
                       then '%'
                     else substring(@x,@i,1) end
  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='5%，12.5%，15%，16.5,40.6%'
select dbo.fn_getmaxnum(@x) '数字最大值'
 
/*
数字最大值
----------
40.6

(1 row(s) affected)
*/


-- 测试3
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)
*/

#12

----新增函数，替换非数字，非'.',并把数据转换为数字
IF  EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID=OBJECT_ID('CharReplace'))

DROP  FUNCTION [dbo].[CharReplace]
GO
CREATE FUNCTION [dbo].[CharReplace]  
(  
    @S VARCHAR(8000)    --字符串  
)  
RETURNS FLOAT AS  
BEGIN  
DECLARE @Result FLOAT

WHILE PATINDEX('%[^0-9,^.]%',@S)>0
BEGIN
SET @S=STUFF(@S,PATINDEX('%[^0-9,^.]%',@S),1,'')
END

    RETURN CONVERT(DECIMAL(12,2),@s)
END 

GO
 

DECLARE @TB TABLE(S VARCHAR(50))
INSERT INTO @TB
        ( S )
SELECT '15%'
UNION ALL
SELECT '16.5%'
UNION ALL
SELECT '25%'
UNION ALL
SELECT '29%'
UNION ALL
SELECT '30%'
UNION ALL
SELECT '中国20.5'

SELECT MAX(dbo.CharReplace(s)) FROM @TB

结果：30



把'%'当字符串处理了

#13

--創建函數
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')

#14

declare @a varchar(max)
set @a='15%、16.5%，25%、29%、30%，中国20.5 '
;with a as(
select row_number() over(order by number)c,number,substring(@a,number,1) a
from master..spt_values
where type='p' and substring(@a,number,1) 
in('1','2','3','4','5','6','7','8','9','0','.'))
select top 1(select ''+a from a 
where b.number-b.c=number-c order by number for xml path('')) 
from a b 
order by 
convert(float,(select ''+a from a 
where b.number-b.c=number-c order by number for xml path(''))) desc

#15

谢谢大家的帮助，在后采用了斑竹的答案，稍微处理了一下。

#16

alter 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,163,44)
     or (substring(@x,@i,1) in('、','，','；')))
  begin
   select @s=@s+case when substring(@x,@i,1) in('，',',','、','；') and (right(@s,1)='%' or @s='') 
                       then '' 
                     when substring(@x,@i,1) in('，',',','、','；')
                       then '%'
                     else substring(@x,@i,1) end
  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