Use regular expressions to implement% and _ wildcard in a like statement in SQL

In the project, we often encounter the case that the data of the database is filtered again after it is taken. LINQ to entity provides a unified query interface and can do the work efficiently, but there is no support for wildcards such as% and _ that we often use in SQL. We can only use the String.contains method to achieve a simple wildcard. Using the String.contains method does not achieve the purpose of using wildcards in query strings. Although the regular expression is obscure, it is very powerful and solves more than one wildcard character.

The code is as follows:

    public static class LINQHelper    {         /// <summary>        // / the all regex meta chars        ///  </summary>        private static string[] regex_ meta_chars = {  "\ \",  ".",  "^",  "$",  "*",  "+",  "?",  "{",   "}",  "(",  ")",  "[",  "]"  };        ///  <summary>        /// Like method work  as sql like        /// </summary>         /// <param name= "SearchString" >The search string </param>  &nBsp;     /// <param name= "Sqlpattern" >the sql pattern</ Param>        /// <returns>whether match or  not</returns>        public static bool  Like (This string searchstring, string sqlpattern)          {            if  (searchstring  == null)             {                 return false;             }             else            {                 string convertedpattern =  Escaperegexmetachars (Sqlpattern). Replace ("_",  "."). Replace ("%",  ". *");                 convertedpattern = string.format ("^{0}$",  convertedpattern);                 return regex.ismatch ( Searchstring, convertedpattern, regexoptions.singleline);             }        }         /// <summary>        /// Like  method work as sql like        /// </ Summary>        /// <param name= "SearchString ">The search string</param>        ///  <param name= "Sqlpattern" >The SQL pattern</param>         /// <param name= "Escapechar" >The escape char</param>         /// <returns>whether match or not</ Returns>        public static bool like (this  String searchstring, string sqlpattern, char escapechar)          {            if  ( Searchstring == null)             {                 return false;             }             else            {                 string convertedPattern =  Escaperegexmetachars (Sqlpattern);                 convertedpattern = replacewildcards (convertedpattern,  ' _ ',  ".",  escapechar);                 convertedpattern = replacewildcards (convertedpattern,  '% ',  ". *",  escapeChar);                  Convertedpattern = string.format ("^{0}$",  convertedpattern);                  return regex.ismatch (Searchstring, convertedpattern, regexoptions.singleline );            }         }        /// <summary>         /// replace wildcards        /  </summary>        /// <param name= " Replacement ">The replacement string</param>          <param name= "Wildcard" >The wildcard</param>         /// <param name= "Replaceto" >the replace wild char to</param >        /// <param name= "EscapeChar" >The  Escape char</param>        /// <returns>the converted search  value</returns>        private static string  Replacewildcards (string replacement, char wildcard, string replaceto, char  Escapechar)         {             string regexexpression = string.format ("(^|[ ^{0}]) ({1}+) ",  escapechar, wildcard);             return regex.replace (replacement, regexexpression, match =>  String.Format ("{0}{1}",  match. GROUPS[1]. Value, match. GROUPS[2]. Value.replace (wildcard. ToString (),  replaceto))                  . Replace (String. Format ("{0}{1}", escapechar, Wildcard),  wildcard. ToString ());         }        /  <summary>        /// escape regex meta  chars        /// </summary>         /// <param name= "Replacement" >the replacement string</ param>        /// <returns>the converted  search value</returns>        private static  String escaperegexmetachars (string replacement)         {             string resultString =  replacement;            foreach  (string  MetachaR in regex_meta_chars)             {                 resultstring  = resultstring.replace (metachar, string. Format (@ "\{0}",  metachar));             }             return resultString;         }    }

First, to escape the metacharacters of all regular expressions in the query string to ordinary characters, it is safe to use regular expressions for matching.

Then, replace "_" and "%" with the corresponding regular expression, "_" and replace ".", "%" with ". *". It is also considered that the SQL like statement also has the escape character function, that is, if the escape clause is used, the "_" and "%" after the like string are converted to ordinary characters instead of wildcards. So when using the escape character, it is handled as follows:

• Replaces all wildcard characters that are not guided by an escape character.

• The escape character of the wildcard that is guided by the escaped character is removed, and the wildcard escape is a normal character.

Here are some examples of conversions:

• Like ' A_b ' converted to A.B

• Like ' a%b ' converted to A.*b

• Like ' a~_b ' ESCAPE ' ~ ' converted to A_b

• The like ' a.b ' is converted to a A/. B

Advantage: We can use the like method to filter the data conveniently in the conditions of the LINQ statement, and the LINQ statement will remain very readable overall.

Cons: The like method is called n times (n depends on the amount of data), and the code that parses the SQL pattern to the regular expression pattern is repeated n times. Therefore, when the amount of data is too large, parsing pattern consumes some resources. Of course, this can be solved by some methods, such as cache parsing results, or instead of the parsed regular expression.

This article is from the "only text cut time" blog, please be sure to keep this source http://arthurshayne.blog.51cto.com/3491820/1547826

Use regular expressions to implement% and _ wildcard in a like statement in SQL