create table #lfy(s varchar(50)) go insert into #lfy select '2%45%1' union all select '3%3%12' union all select '6%67%5' union all select '4%45%23'select case when e>c then e else c end as fld1 from (select case when a>b then a else b end as e,c from (select convert(int,left(s,charindex('%',s,0)-1)) as a ,convert(int,substring(s,charindex('%',s,0)+1,charindex('%',s,charindex('%',s,0)+1)-charindex('%',s,0)-1)) as b ,convert(int,right(s,len(s)-charindex('%',s,charindex('%',s,0)+1))) as c from #lfy) as d) as g go
create function f_max(@str varchar(300)) returns float as begin declare @a1 varchar(10) declare @a2 varchar(10) declare @a3 varchar(10) declare @re float declare @substr varchar(30),@pos int select @str=@str select @substr='%' select @pos=charindex(@substr,@str) select @a1=substring(@str,1,@pos-1); select @str=substring(@str,@pos+1,len(@str)-@pos) select @pos=charindex(@substr,@str) select @a2=substring(@str,1,@pos-1) select @a3=substring(@str,@pos+1,len(@str)) select @re=max(t.a) from (select cast(@a1 as int) as a union all select cast(@a2 as int) as a union all select cast(@a3 as int) as a ) t return @reend 具体调用时 select dbo.f_max(fld1) from table1 就OK了
-- create table table1(a varchar(30)) insert into table1 select '10%20%30' union all select '40%50%60' union all select '70%80%90' drop table table1 select dbo.f_max(a) from table1
drop table #lfy go create table #lfy(s varchar(50)) go insert into #lfy select '2%45%1%456' union all select '3%3%12' union all select '6%67%5' union all select '4%45%23' union all select '2345%34%4546%458965%-8543837485%97' union all select '34564657676%%' union all select '%' union all select '2354345%' union all select '%3245' go drop function f_max go create function f_max(@str varchar(8000),@strsub varchar(50)) returns float as begin declare @temp varchar(18) declare @len int declare @re float declare @substr varchar(50),@pos int select @re= -exp(709) if (len(@str)=0 or len(@strsub)=0 or @str=@strsub) return -exp(709) else begin select @str=@str+@strsub select @substr=@strsub while(len(@str)>0) begin select @pos=charindex(@substr,@str) select @temp=substring(@str,1,@pos-1); select @re=case when convert(bigint,@temp)>@re then convert(bigint,@temp) else @re end select @str=substring(@str,@pos+len(@substr),len(@str)-@pos-len(@strsub)+1) end end return @re end go --select case when dbo.f_max(s,'%')>-exp(709) then dbo.f_max(s,'%') else 0 end from #lfy declare @st varchar(50) select @st='%' select dbo.f_max(s,@st) from #lfy go
drop table #lfy go create table #lfy(s varchar(50)) go insert into #lfy select '2%45%1%456' union all select '3%3%12' union all select '6%67%5' union all select '4%45%23' union all select '2345%34%4546%458965%-8543837485%97' union all select '34564657676%%' union all select '%' union all select '2354345%' union all select '%3245' go drop function f_max go create function f_max(@str varchar(8000),@strsub varchar(50)) returns float as begin declare @temp varchar(18) declare @len int declare @re float declare @con float select @con=-exp(709) declare @substr varchar(50),@pos int select @re= @con if (len(@str)=0 or len(@strsub)=0 or @str=@strsub) return @con else begin select @str=@str+@strsub select @substr=@strsub while(len(@str)>0) begin select @pos=charindex(@substr,@str) select @temp=substring(@str,1,@pos-1); select @re=case when convert(bigint,@temp)>@re then convert(bigint,@temp) else @re end select @str=substring(@str,@pos+len(@substr),len(@str)-@pos-len(@strsub)+1) end end return @re end go --select case when dbo.f_max(s,'%')>-exp(709) then dbo.f_max(s,'%') else 0 end from #lfy declare @st varchar(50) select @st='%' select dbo.f_max(s,@st) from #lfy go --也可以适当的加第三个参数,如把比较符做为参数,可以比较>,<,= --也可以加上第四个参数,那就@con这个比较的基数
下面把函数改成通用的了 Create function f_max(@str varchar(300)) returns float as begin declare @re float select @re=0 declare @substr varchar(30),@pos int select @pos=1 select @substr='%' while (@pos<>0) begin select @pos=charindex(@substr,@str) if (@pos=0) begin if (@re<cast(@str as float)) select @re=cast(@str as float) Break end else begin if (@re<cast(substring(@str,1,@pos-1) as float)) begin select @re=cast(substring(@str,1,@pos-1) as float) select @str=substring(@str,@pos+1,len(@str)-@pos) end Continue end end return @re end加点分吧,呵呵,我在sql 版还没有赚几分呢.sql 2000,win2000 server验证成功
go
insert into #lfy
select '2%45%1'
union all
select '3%3%12'
union all
select '6%67%5'
union all
select '4%45%23'select case when e>c then e else c end as fld1
from
(select case when a>b then a else b end as e,c
from
(select convert(int,left(s,charindex('%',s,0)-1)) as a
,convert(int,substring(s,charindex('%',s,0)+1,charindex('%',s,charindex('%',s,0)+1)-charindex('%',s,0)-1)) as b
,convert(int,right(s,len(s)-charindex('%',s,charindex('%',s,0)+1))) as c
from #lfy) as d) as g
go
returns float
as
begin
declare @a1 varchar(10)
declare @a2 varchar(10)
declare @a3 varchar(10)
declare @re float declare @substr varchar(30),@pos int
select @str=@str
select @substr='%'
select @pos=charindex(@substr,@str)
select @a1=substring(@str,1,@pos-1);
select @str=substring(@str,@pos+1,len(@str)-@pos)
select @pos=charindex(@substr,@str)
select @a2=substring(@str,1,@pos-1)
select @a3=substring(@str,@pos+1,len(@str)) select @re=max(t.a) from (select cast(@a1 as int) as a
union all select cast(@a2 as int) as a
union all select cast(@a3 as int) as a ) t
return @reend
具体调用时
select dbo.f_max(fld1) from table1 就OK了
create table table1(a varchar(30))
insert into table1 select '10%20%30'
union all select '40%50%60'
union all select '70%80%90'
drop table table1
select dbo.f_max(a) from table1
如果不一定是三个呢?比如有的4个,有的5个,又该怎么处理呢??
go
create table #lfy(s varchar(50))
go
insert into #lfy
select '2%45%1%456'
union all
select '3%3%12'
union all
select '6%67%5'
union all
select '4%45%23'
union all
select '2345%34%4546%458965%-8543837485%97'
union all
select '34564657676%%'
union all
select '%'
union all
select '2354345%'
union all
select '%3245'
go
drop function f_max
go
create function f_max(@str varchar(8000),@strsub varchar(50))
returns float
as
begin
declare @temp varchar(18)
declare @len int
declare @re float
declare @substr varchar(50),@pos int
select @re= -exp(709)
if (len(@str)=0 or len(@strsub)=0 or @str=@strsub) return -exp(709) else begin
select @str=@str+@strsub
select @substr=@strsub
while(len(@str)>0)
begin
select @pos=charindex(@substr,@str)
select @temp=substring(@str,1,@pos-1);
select @re=case when convert(bigint,@temp)>@re then convert(bigint,@temp) else @re end
select @str=substring(@str,@pos+len(@substr),len(@str)-@pos-len(@strsub)+1)
end
end
return @re
end
go
--select case when dbo.f_max(s,'%')>-exp(709) then dbo.f_max(s,'%') else 0 end from #lfy
declare @st varchar(50)
select @st='%'
select dbo.f_max(s,@st) from #lfy
go
go
create table #lfy(s varchar(50))
go
insert into #lfy
select '2%45%1%456'
union all
select '3%3%12'
union all
select '6%67%5'
union all
select '4%45%23'
union all
select '2345%34%4546%458965%-8543837485%97'
union all
select '34564657676%%'
union all
select '%'
union all
select '2354345%'
union all
select '%3245'
go
drop function f_max
go
create function f_max(@str varchar(8000),@strsub varchar(50))
returns float
as
begin
declare @temp varchar(18)
declare @len int
declare @re float
declare @con float
select @con=-exp(709)
declare @substr varchar(50),@pos int
select @re= @con
if (len(@str)=0 or len(@strsub)=0 or @str=@strsub) return @con else begin
select @str=@str+@strsub
select @substr=@strsub
while(len(@str)>0)
begin
select @pos=charindex(@substr,@str)
select @temp=substring(@str,1,@pos-1);
select @re=case when convert(bigint,@temp)>@re then convert(bigint,@temp) else @re end
select @str=substring(@str,@pos+len(@substr),len(@str)-@pos-len(@strsub)+1)
end
end
return @re
end
go
--select case when dbo.f_max(s,'%')>-exp(709) then dbo.f_max(s,'%') else 0 end from #lfy
declare @st varchar(50)
select @st='%'
select dbo.f_max(s,@st) from #lfy
go
--也可以适当的加第三个参数,如把比较符做为参数,可以比较>,<,=
--也可以加上第四个参数,那就@con这个比较的基数
Create function f_max(@str varchar(300))
returns float
as
begin
declare @re float
select @re=0
declare @substr varchar(30),@pos int
select @pos=1
select @substr='%'
while (@pos<>0)
begin
select @pos=charindex(@substr,@str)
if (@pos=0)
begin
if (@re<cast(@str as float))
select @re=cast(@str as float)
Break
end
else
begin
if (@re<cast(substring(@str,1,@pos-1) as float))
begin
select @re=cast(substring(@str,1,@pos-1) as float)
select @str=substring(@str,@pos+1,len(@str)-@pos)
end
Continue
end
end
return @re
end加点分吧,呵呵,我在sql 版还没有赚几分呢.sql 2000,win2000 server验证成功