ALTER function [dbo].[Zfun_bage](@UDF02 char(40)) returns intas--把包裝方法自動計算成件數BEGIN declare @A CHAR(20) declare @num intselect top 1 @A=UDF02 from COPTH WHERE UDF02=@UDF02if (CHARINDEX('*',@A)>0 AND CHARINDEX('+',@A)<=0)beginSET @num= CONVERT(int,substring(@A,CHARINDEX('*',@A)+1,7)) endiF(CHARINDEX('*',@A)>0 AND CHARINDEX('+',@A)>0) beginif(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)) endif(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)) endif(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)) endif(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)) endendif(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=1IF(@A LIKE '%同上%'or @A='')set @num=0return @numEND
