create function GetNumber( @str varchar(1000), @site int ) returns int as begin declare @n int,@a varchar(1); set @n=0; set @a = substring(@str,@site,1); if ISNUMERIC(@a)=1 set @n =cast(@a as int); return @n; end GOcreate function GetResult( @code varchar(1000) ) returns varchar(1000) as begin declare @a int,@a2 int,@b int,@b2 int; declare @result varchar(20),@len int; set @result = ''; while (len(@code)>=4) --如果长度小于4位,就不用比了 begin select @a = (dbo.GetNumber(@code,1)+ dbo.GetNumber(@code,3))%10,@b =(dbo.GetNumber(@code,2)+ dbo.GetNumber(@code,4))%10, @a2 =dbo.GetNumber(@code,5),@b2 =dbo.GetNumber(@code,6); if @a <=2 and @b <= 2 select @a= (@a+@a2)%10,@b= (@b+@b2)%10; else begin if @a=3 begin if @a2=8 set @a= (@a+@a2)%10; else select @a= (@a+@a2)%10,@b= (@b+@b2)%10; end end if @a > @b set @result =@result +'甲大'; else set @result =@result +'乙大'; set @len = len(@code); if @len<=6 set @code ='' else set @code = substring(@code,7,@len-6); end return @result; end goselect ID,dbo.GetResult(相关) from 表1 写了个函数 GetResult 来产生结果,GetNumber这个小函数是为了方便使用。
@str varchar(1000),
@site int
) returns int
as
begin
declare @n int,@a varchar(1);
set @n=0;
set @a = substring(@str,@site,1);
if ISNUMERIC(@a)=1 set @n =cast(@a as int);
return @n;
end
GOcreate function GetResult(
@code varchar(1000)
) returns varchar(1000)
as
begin
declare @a int,@a2 int,@b int,@b2 int;
declare @result varchar(20),@len int; set @result = ''; while (len(@code)>=4) --如果长度小于4位,就不用比了
begin select @a = (dbo.GetNumber(@code,1)+ dbo.GetNumber(@code,3))%10,@b =(dbo.GetNumber(@code,2)+ dbo.GetNumber(@code,4))%10,
@a2 =dbo.GetNumber(@code,5),@b2 =dbo.GetNumber(@code,6);
if @a <=2 and @b <= 2
select @a= (@a+@a2)%10,@b= (@b+@b2)%10;
else
begin
if @a=3
begin
if @a2=8
set @a= (@a+@a2)%10;
else
select @a= (@a+@a2)%10,@b= (@b+@b2)%10;
end
end
if @a > @b
set @result =@result +'甲大';
else
set @result =@result +'乙大';
set @len = len(@code);
if @len<=6
set @code =''
else
set @code = substring(@code,7,@len-6);
end
return @result;
end
goselect ID,dbo.GetResult(相关) from 表1
写了个函数 GetResult 来产生结果,GetNumber这个小函数是为了方便使用。