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 intas 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    endend

select school_name , ......from bas_infowhere    dbo.fn_str_fuzzy_qry(school_name,N‘樹林國小‘,1) > 0order by dbo.fn_str_fuzzy_qry(school_name,N‘樹林國小‘,1) desc