SQL Server string left matching

In SQL Server, where fuzzy matching strings are often used, the simplest way is to use the LIKE keyword (like syntax http://msdn.microsoft.com/en-us/library/ms179859.aspx). But if we use the method of adding percent before and after, there is no way to use the index for fast query, so in many cases we use the left matching way. The most common example is in the search box, the user entered a part of the keyword, the system can be the user input to the left to match, to find out the relevant results listed. The advantage of using a left match is that you can use the index established in SQL Server to make the query efficient, but bad SQL statements still cause the index to become unusable.

Suppose we now have a table Ycmrsale, where there is a field Matnr store the material number information, if we want to query from this table with AB starting with the material number, if the use of nhibernate, then we commonly used to:

//queryover notation  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 syntax  result = session. Createcriteria<ycmrsale> (). ADD (Expression.like ( "MATNR" ,  "AB" , Matchmode.start)). List<ycmrsale> (); 

These kinds of writing are essentially generated by the following where condition statement:

where  like ' ab% '

If you use EntityFramework, then the C # code of the query is similar to NHibernate:

var result = BwEntities.YCMRSALEs.Select (s = = S.matnr). Where (s = = S.startswith ("AB"));

The Where condition is the same:

WHERE  like ' ab% '

Here is only the simplest case, if we want to query the material number itself contains%, such as to query the "%00" the beginning of the material number, then how to ensure that the percent here is a percent sign rather than the meaning of fuzzy matching?

Using EntityFramework is simple and requires no modification, and the system generates different SQL statements based on the incoming string:

var result = BwEntities.YCMRSALEs.Select (s = = S.matnr). Where (s = = S.startswith ("%00"));

Generated SQL WHERE Condition:

WHERE  like ' ~%00% ' ESCAPE ' ~ '

For developers, it's really simple, no input. But if the use of nhibernate will be troublesome, we have to determine whether the user input string inside a special transfer character, if there is, then need to replace, and C # query statements are different.

string "%00"; Regex regex=New regex (@ "[~%\[\]_]" "delegatereturn" ~ " + M.value;}); var result = session. Queryover<ycmrsale> (). Whererestrictionon (c = c.matnr). Islike (input, Matchmode.start,' ~ '). List<ycmrsale> ();

Generated SQL WHERE Condition:

WHERE  like Escape ' ~ ' ' ~%00% '

All of this is done in the Ormapping tool to make a left-matching query, if we want to query the SQL statement directly, there is a way to do is to use the right-hand function. Also in the Ycmrsale table example, if we have another table Matnr, the MATNR column in the table stores an incomplete material number, now need to join two tables, using the MATNR column for the left match, then our SQL can be written as:

Select * from Ycmrsale sinnerjoin matnr mto Left (S.matnr,len ( M.MATNR)) =m.matnr

This can get the result we want, but because the function is used for the MATNR column, the index cannot be used, so the query is slow.

If we want to rewrite the form like, then we need to work on the Matnr column in the MATNR table, replace the special characters, and replace the words nonalphanumeric ~%_[]. So our SQL query will turn out like this:

Select * from Ycmrsale sinnerjoins matnr m on like replace (replace (Replace (replace (M.MATNR,' ~ ',' ~ ~ '),' _ ',' ~_ '),' [',' ~['),'] ',' ~] '),'% ', '~% ') +'% 'escape  ' ~ '

Although the SQL here looks more ugly, it can use the index set on the Ycmrsale table on the MATNR, so it is more efficient.

In addition to the handling of the keyword escape, Microsoft has also given another solution, which is to use "[]" to enclose the escaped character. This is simpler than the escape keyword, and the corresponding SQL is:

select  *from  ycmrsale S inner  join  matnr mon  s.matnr like  replace ( Replace (replace (m.matnr,, ),  ' [' , ),  '% ' ,  ' [%] ' ) + 

We can even write a custom function to deal with the transfer character in the case of join, which is very complicated ...

Create functionOPSTR (@input varchar(50))returns varchar(100) asbeginDeclare@iint= 1;Declare@result varchar(100) ="';Declare@cChar(1) while(@i<=len (@input))begin    Set@c=substring(@input, @i,1);if(@c=' [' or@c='% ' or@c=' _ ')begin        Set@result+=' ['[Email protected]+'] ';End    Else    begin        Set@result[Email protected];End    Set@i+=1;Endreturn@resultEnd

The custom function is then called in the query.

Select * from Ycmrsale sinnerjoins matnr m on like dbo. Opstr (M.MATNR) +'% '