create table mucai(材质 varchar(20), 尺寸 varchar(20))
insert mucai
select '榆木','22*75*75' union all
select '榆木','52*48*48' union all
select '柞木','78*89*199'
--要求:判断宽和高是否相同,如果相同则返回“板榆木”,如果不同则返回材质的原名,
go
create function panduan
(
@str varchar(20)
)returns int
as
begin
declare @gao varchar(5),@kuan varchar(5),@i int
select @kuan=parsename(replace(@str,'*','.'),2),@gao=parsename(replace(@str,'*','.'),1)
if(@kuan=@gao)
set @i=1 --相同
else
set @i=0 --不同
return(@i)
end
go
select 材质=case dbo.panduan(尺寸) when 1 then '板榆木' else 材质 end,
尺寸
from mucai
CREATE function fn_CheckWidthEqualHeight(@材质 nvarchar(16))
returns nvarchar(16)
as
begin
declare @re nvarchar(16)
select @re =(case when width=height then '板榆木' else 材质 end)
from
(
select 材质,
尺寸(长*宽*高)),
substring(尺寸(长*宽*高),l1+1,l2-l1-1) as width,
substring(尺寸(长*宽*高),l2+1,l3-l2) as height
from
(
select 材质,
尺寸(长*宽*高)),
charindex('*',尺寸(长*宽*高)) as l1,
charindex('*',尺寸(长*宽*高),charindex('*',尺寸(长*宽*高)+1) as l2,
len(尺寸(长*宽*高)) as l3
)t
)t
return @re
end
declare @T varchar(50)
declare @TW int
declare @TH int
set @T='22*48*48'
set @T=right(@T,len(@T)-charindex('*',@T))
set @TW=cast(left(@T,charindex('*',@T)-1) as int)
set @TH=cast(right(@T,len(@T)-charindex('*',@T)) as int)
if @TW=@TH
print '板榆木'
else
print '榆木'
select case(when when substing(材质,1,charindex('*',材质))=subtring( substing(stuff(材质,1,charindex('*'),''),1,charindex('*',stuff(材质,1,charindex('*'),''))) then '板'+材质 else'材质' end ) , 尺寸
from 表