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