ALTER function [dbo].[Zfun_bage](@UDF02 char(40)) returns int
as
--把包装方法自动计算成件数
BEGINdeclare @A CHAR(20)
declare @num int
select top 1 @A=UDF02 from COPTH WHERE UDF02=@UDF02
if (CHARINDEX('*',@A)>0 AND CHARINDEX('+',@A)<=0)
begin
SET @num= CONVERT(int,substring(@A,CHARINDEX('*',@A)+1,7)) end
iF(CHARINDEX('*',@A)>0 AND CHARINDEX('+',@A)>0)
begin
if(LEN(@A)-LEN(REPLACE(@A,'+',''))=1 and LEN(@A)-LEN(REPLACE(@A,'*',''))=1 )
begin
set @num=1+CONVERT(INT,SUBSTRING(@A,CHARINDEX('*',@A)+1,CHARINDEX('+',@A)-CHARINDEX('*',@A)-1)) end
if(LEN(@A)-LEN(REPLACE(@A,'+',''))=2 and LEN(@A)-LEN(REPLACE(@A,'*',''))=1 )
begin
set @num=2+CONVERT(INT,SUBSTRING(@A,CHARINDEX('*',@A)+1,CHARINDEX('+',@A)-CHARINDEX('*',@A)-1)) end
if(LEN(@A)-LEN(REPLACE(@A,'+',''))=3 and LEN(@A)-LEN(REPLACE(@A,'*',''))=1 )
begin
set @num=3+CONVERT(INT,SUBSTRING(@A,CHARINDEX('*',@A)+1,CHARINDEX('+',@A)-CHARINDEX('*',@A)-1)) end
if(LEN(@A)-LEN(REPLACE(@A,'+',''))=4 and LEN(@A)-LEN(REPLACE(@A,'*',''))=1 )
begin
set @num=4+CONVERT(INT,SUBSTRING(@A,CHARINDEX('*',@A)+1,CHARINDEX('+',@A)-CHARINDEX('*',@A)-1)) end
end
if(CHARINDEX('*',@A)<=0 and (LEN(@A)-LEN(REPLACE(@A,'+','')))>0 )
SET @num=1+LEN(@A)-LEN(REPLACE(@A,'+',''))
if(CHARINDEX('*',@A)<=0 and CHARINDEX('+',@A)<=0)
SET @num=1
IF(@A LIKE '%同上%'or @A='')
set @num=0
return @num
END