Comparison of SQL Server similarity

Transferred from: http://www.dotblogs.com.tw/rachen/archive/2008/10/07/5611.aspx

function one. To produce a like comparison with a string

Create functionFN_GET_FUZZY_STR (@instr nvarchar( the) )returns nvarchar(513) as begin    /*according to the string to be loaded with the% symbol*/    /*declare @instr nvarchar (256); Set @instr = N ' Forest min '; */    Declare @outstr nvarchar(513)        if IsNull(@instr,"')= "' begin  Set @outstr = "'; End Else begin  Declare @i int; Set @i = 1; Set @outstr = '%';  while @i <= Len(@instr)begin      Set @outstr = @outstr + substring(@instr,@i,1)+ '%'; Set @i = @i + 1; End    End    return @outstr;End

Functions two. Enquiry function

Create functionFn_str_fuzzy_qry (@src_str nvarchar( the) ,@match_str nvarchar( the) ,@setp int  )returns int as begin    /*The similarity of strings is higher than that of the results.*/    /*declare @src_str nvarchar (256);--Declare @match_str nvarchar (256);--string declare @setp int; --minus a few words at a time*/    Declare @fuzzy_str nvarchar(513); Declare @like_str  nvarchar(513); Set @fuzzy_str =DBO.FN_GET_FUZZY_STR (@match_str); return  Case   when @src_str  like @fuzzy_str  Then      4000 +  + - Len(@src_str)   when(Len(@fuzzy_str)- @setp*2*1 >= 5) and @src_str  like  Left(@fuzzy_str,Len(@fuzzy_str)- @setp*2*1) Then       the +  + - Len(@src_str)   when(Len(@fuzzy_str)- @setp*2*2 >= 5) and @src_str  like  Left(@fuzzy_str,Len(@fuzzy_str)- @setp*2*2) Then       - +  + - Len(@src_str)   when(Len(@fuzzy_str)- @setp*2*3 >= 5) and @src_str  like  Left(@fuzzy_str,Len(@fuzzy_str)- @setp*2*3) Then       + +  + - Len(@src_str)  Else 0    EndEnd

How to use

Select school_name, ...  from Bas_info where     dbo.fn_str_fuzzy_qry (school_name,n' Grove small ',1> 0 Order  by Dbo.fn_str_fuzzy_qry (school_name,n' Grove small ',1desc 

Comparison of SQL Server similarity