SQL Server字串左匹配

深藍發表於2014-05-15
SQLServer字串

在SQL Server中經常會用到模糊匹配字串的情況，最簡單的辦法就是使用like關鍵字（like語法http://msdn.microsoft.com/en-us/library/ms179859.aspx）。但是如果我們使用的前後都加%的方式，是沒辦法用到索引進行快速查詢的，所以很多情況下我們使用左匹配的方式。最常見的一個例子就是在搜尋框中，使用者輸入了一部分關鍵字，系統可以通過使用者的輸入進行左匹配，找出相關的結果列出來。使用左匹配的好處是可以使用到SQL Server中對該欄位建立的索引，使得查詢效率很高，但是不好的SQL語句仍然會導致索引無法使用。

假設我們現在有個表YCMRSALE，其中有個欄位MATNR儲存了料號資訊，如果我們要從這個表中查詢出以AB開頭的料號，如果使用NHibernate，那麼我們常用的寫法有：

//QueryOver的寫法
var result = session.QueryOver<Ycmrsale>().WhereRestrictionOn(c => c.Matnr).IsLike("AB", MatchMode.Start).List<Ycmrsale>();
//Linq to NHibernate
result = session.Query<Ycmrsale>().Where(c => c.Matnr.StartsWith("AB")).ToList();
//Criteria寫法
result = session.CreateCriteria<Ycmrsale>().Add(Expression.Like("Matnr", "AB", MatchMode.Start)).List<Ycmrsale>();

這幾種寫法本質上都是生成了如下的where條件語句:

where ycmrsale0_.Matnr like 'AB%'

如果使用EntityFramework,那麼查詢的C#程式碼也和NHibernate類似:

var result = bwEntities.YCMRSALEs.Select(s => s.MATNR).Where(s => s.StartsWith("AB"));

where條件也是一樣的:

WHERE [Extent1].[MATNR] LIKE 'AB%'

這裡只是舉了最簡單的情況,如果我們要查詢的料號本身就包含%,比如要查詢以”%00”開頭的料號，那麼怎麼保證這裡的%是百分號而不是表示模糊匹配的意思呢？

使用EntityFramework就很簡單，什麼都不需要修改，系統會根據傳入的字串生成不同的SQL語句：

var result = bwEntities.YCMRSALEs.Select(s => s.MATNR).Where(s => s.StartsWith("%00"));

生成的SQL Where條件：

WHERE [Extent1].[MATNR] LIKE '~%00%' ESCAPE '~'

對開發人員來說，真是很簡單，什麼輸入都不用管。但是如果用NHibernate就要麻煩點了，我們必須要判斷使用者輸入的字串裡面是否有特殊轉移符，如果有，那麼就需要進行替換，而且C#查詢語句也有所不同。

string input = "%00";
Regex regex=new Regex(@"[~%\[\]_]");
input= regex.Replace(input, delegate(Match m) { return "~" + m.Value; });
var result = session.QueryOver<Ycmrsale>().WhereRestrictionOn(c => c.Matnr).IsLike(input, MatchMode.Start,'~').List<Ycmrsale>();
生成的SQL Where條件：
WHERE this_.Matnr like @p0 escape '~';@p0 = '~%00%'

以上說的都是在ORMapping的工具中進行左匹配查詢，如果我們要在SQL語句中直接進行查詢還有一種寫法就是用left函式。同樣以YCMRSALE表舉例，如果我們有另一表matnr，該表中的matnr列儲存了不完整的料號，現在需要將兩個表join起來，使用matnr列進行左匹配，那麼我們的SQL可以寫成：

select *
from YCMRSALE s
inner join matnr m
on left(s.MATNR,len(m.matnr))=m.matnr

這個寫法能夠得到我們想要的結果，但是由於對MATNR列使用了函式，所以無法使用索引，所以查詢速度很慢。

如果我們要改寫成like的形式，那麼就需要對matnr表中的matnr列進行處理，將特殊字元進行替換，將~%_[]這幾個字元都替換掉。所以我們的SQL查詢就會變成這樣：

select *
from YCMRSALE s
inner join matnr m
on s.MATNR like replace(replace(replace(replace( replace(m.matnr,'~','~~'),'_','~_'),'[','~['),']','~]'),'%','~%')+'%' escape '~'

這裡的SQL雖然看起來比較Ugly,但是卻可以用上YCMRSALE表上對MATNR建立的索引，所以效率較高。

除了ESCAPE這個關鍵字的處理方式外，微軟官方還給出了另一種解決辦法，那就是使用“[]”將轉義字元括起來。這種寫法比escape關鍵字的寫法要簡單點，對應的SQL為：

select *
from YCMRSALE s
inner join matnr m
on s.MATNR like replace(replace(replace( m.matnr,'[','[[]'),'_','[_]'),'%','[%]')+'%'

甚至我們還可以先寫個自定義函式對轉移字元進行處理對於join的情況，那就非常複雜了。。。

create function OpStr(@input varchar(50))
returns varchar(100)
as
begin
declare @i int=1;
declare @result varchar(100)='';
declare @c char(1)
while(@i<=len(@input))
begin
    set @c=substring(@input,@i,1);
    if (@c='[' or @c='%' or @c='_')
    begin
        set @result+='['+@c+']';
    end
    else
    begin
        set @result+=@c;
    end
    set @i+=1;
end
return @result
end
然後在查詢中呼叫這個自定義的函式即可。
select *
from YCMRSALE s
inner join matnr m
on s.MATNR like dbo.OpStr(m.matnr)+'%'

相關文章