SQL Server字符串左匹配

在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)+'%'