sqlserver 车牌识别车牌号模糊匹配数

*注  一般情况下第一位汉字不处理

/*按顺序检查@stra,@strb相同字符匹配数
   select dbo.UFCompareString('A90U4R','A90U6P')
*/
create function [dbo].[UFCompareString]

(
       @stra nvarchar(100),
       @strb nvarchar(100)

)
returns int as
begin


    declare @strbase nvarchar(100)
    declare @rank int
    declare @strLen int
    select @rank=0

    --定义循环变量   
 
    DECLARE @i INT   
      
    SET @i=1  
    Set @strlen = len(@stra)
    

    WHILE @i<=@strlen   
      
    BEGIN   
      
       IF CHARINDEX(substring(@stra,@i,1),@strb,@i)=@i
       begin  
          set @rank = @rank +1
       end
      
      SET @i=@i+1   
      
    END   

    return @rank

end
go

 

--方式2

/*连续两个字符相匹配数*/
If Exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ufSplitChar]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[ufSplitChar]
GO

/*
按顺序每两个字符拆分成表数据
*/
create function [dbo].[ufSplitChar](@s varchar(1000) )

returns table

as
return
 (
 
  --select right(@s,1) as item
  --union all
  select substring(@s,number+1,2)as item

  from master..spt_values

  where type='p' and number<len(@s)-1)
go

If Exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ufCompareString]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[ufCompareString]
GO

/*
检查两字符拆分表相同数
*/
create function [dbo].[ufCompareString]

(
       @stra nvarchar(100),
       @strb nvarchar(100)

)
returns int as
begin


    declare @strbase nvarchar(100)
    declare @rank int

    select @rank=0

       if len(@stra)>len(@strb)

          select @rank=count(*)

          from dbo.ufSplitChar(@strb)

          where item in(select item from dbo.ufSplitChar(@stra))

       else

          select @rank=count(*)

          from dbo.ufSplitChar(@stra)

          where item in(select item from dbo.ufSplitChar(@strb))

       return @rank

end
go