日期:2014-05-17 浏览次数:21209 次
-- 建函数
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