对号码进行分类的触发器

   在项目中用到的对号码的类别进行识别,如:AAAA,ABAB等待。
   保留以备后用。
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE TRIGGER [testTrigger] ON [dbo].[Phone] 
FOR INSERTUPDATE
AS
declare     @Level2 char(10),
    
@Level3 char(10),
    
@phone char(11),
    
@first             char(1),
         
@second       char(1),
         
@third            char(1),
         
@last              char(1)

select @phone=phonenum from inserted
select @first = substring(@phone,8,1)
select @second = substring(@phone,9,1)
select @third = substring(@phone,10,1)
select @last = substring(@phone,11,1)




            
-- 普号无4
            if (charindex('4'@phone )=0)
            
begin
                
set @Level2='普号'
                
set @Level3='无4'
            
end
                       
--差号含4
            if (charindex('4'@phone)!=0)
            
begin
                
set @Level2='差号'
                
set @Level3='号码含4'
            
end

        
--差号尾数含4
            if (charindex('4'substring(@phone,8,5) )!=0)
            
begin
                
set @Level2='差号'
                
set @Level3='尾数含4'
            
end
            
--134
            if (substring(@phone,0,4="134")
            
begin
                
set @Level2='差号'
                
set @Level3='134号段'
            
end




                
            
--ABAB
            if (@first=@third and @second=@last and @first<>@second )
            
begin
                
set @Level2='特号'
                
set @Level3='ABAB'
            
end
    
                                
--AABB
            if (  @first=@second and @third=@last and @first<>@third )
            
begin
                
set @Level2='特号'
                
set @Level3='AABB'
            
end


            
--ABC
            if (  convert(int,@last)=convert(int,@third)+1 and convert(int,@third)=convert(int,@second)+1  and  charindex('4',@phone)=0)
                
begin
                    
set @Level2='优选号码'
                    
set @Level3='ABC'
                
end
            

            
--ABCD
            if ( convert(int,@last)=convert(int,@third)+1 and convert(int,@third)=convert(int,@second)+1 and convert(int,@second)=convert(int,@first)+1 )
            
begin
                
set @Level2='特号'
                
set @Level3='ABCD'
            
end
            
            

            
--AAAB
            if (@first=@second and @second=@third and @third<>@last and  charindex('4',@phone)=0)
            
begin
                
set @Level2='优选号码'
                
set @Level3='AAAB'
            
end

            
--ABBA
            if (@first=@last and @second=@third and @first<>@second and  charindex('4',@phone)=0)
            
begin
                
set @Level2='优选号码'
                
set @Level3='ABBA'
            
end


            
--AA
            if (@third=@last and  charindex('4',@phone)=0)
                
begin
                    
set @Level2='优选号码'
                    
set @Level3='AA'
                
end
            
            
--AAA
                if (@second=@third and @third=@last)
                
begin
                    
set @Level2='特号'
                    
set @Level3='AAA'
                
end

            
--AAAA
            if (@first=@second and @second=@third and @third=@last)
            
begin
                
set @Level2='特号'
                
set @Level3='AAAA'
            
end
            






update phone set phone.hlr=PhoneNumLevel1.hlr,level1=ModelNum,level2=@level2,level3=@level3 
from PhoneNumLevel1 INNER JOIN phone on 
 
right(NumRange,7)=left(phonenum,7)  where phonenum=@phone

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

原文地址:https://www.cnblogs.com/madgoat/p/755744.html