转自：http://www.dotblogs.com.tw/rachen/archive/2008/10/07/5611.aspx
函數一.產生 Like 比對用字串
create function fn_get_fuzzy_str( @instr nvarchar(256) )
returns nvarchar(513)
as begin
/*依據傳入字串補上%符號*/
/*
declare @instr nvarchar(256);
set @instr = N'樹林國民小學'; */ 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
函數二.查詢函數
create function fn_str_fuzzy_qry( @src_str nvarchar(256) , @match_str nvarchar(256) , @setp int )
returns int
as begin
/*字串相似度比對 結果直越大相似度越高*/
/*
declare @src_str nvarchar(256); --比對來源
declare @match_str nvarchar(256); --比對字串
declare @setp int; --每次步減幾個字
*/
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 + 1000 - 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
3000 + 1000 - 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
2000 + 1000 - 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
1000 + 1000 - len(@src_str)
else 0
end end
應用方式
select school_name , ......
from bas_info
where
dbo.fn_str_fuzzy_qry(school_name,N'樹林國小',1) > 0
order by dbo.fn_str_fuzzy_qry(school_name,N'樹林國小',1) desc