Database MySQL wildcard character and pattern matching regular expression

Today, a colleague said he wrote a SQL statement query with no results.

SELECT * from Persons WHERE city like ' [bln]% '

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M01/8D/46/wKioL1iVZbLhkCD0AAA5aAmd5Mo881.jpg-wh_500x0-wm_3 -wmp_4-s_4029323602.jpg "style=" Float:none; "title=" 1.jpg "alt=" Wkiol1ivzblhkcd0aaa5aamd5mo881.jpg-wh_50 "/>

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M02/8D/49/wKiom1iVZbXi2R3EAAAfZDEZ4OU985.jpg-wh_500x0-wm_3 -wmp_4-s_386410488.jpg "style=" Float:none; "title=" 2.jpg "alt=" Wkiom1ivzbxi2r3eaaafzdez4ou985.jpg-wh_50 "/>

Then let's take a second look:

SQL wildcard characters are statements that use special characters or grammatical spellings to perform fuzzy search instructions. When querying data in a database, the SQL wildcard can override one or more characters. Use must be used with the LIKE operator. The wildcard characters that can be used in SQL are:

Wildcard characters	Description
%	Override one or more characters
-	Replaces only one character
[Charlist]	Any one character of the word columns
[^charlist] or [!charlist]	Any single character that is not in the word columns

"%": matches any number of characters (0 or more); "_": matches any single character;

You can use a like or not-like comparison operator when using wildcard characters, but not with = or! =

Take the user table as an example:

Query for records ending with a: SELECT * from user where username like '%a ';

Query user name starting with a: SELECT * from user where username like ' a% ';

The query is not a user name that begins with a: SELECT * from user where username like ' a% ';

Query contains the user name of a: SELECT * from user where username like '%a% ';

Query user name with two characters: SELECT * from user where username like ' __ ';

Here's the point:

mysql pattern matching are the use of extended regular expressions, using the regexp and not regexp operators ( or Rlike and not rlike, then encounter hive to check the like, Rlike).  

Some characters of the extended regular expression are:  

"*"  

In addition, the regular expression is case-sensitive, of course, you can also use a character class colleague matching case, "[AA]" matches lowercase or uppercase "a" and "[A-za-z]" matches any letter of both types.

to locate a pattern to match the start or end of a target value, use "^" at the beginning of the pattern or "$" at the end of the pattern. Here's how to write a like query using regexp:

Query city for people starting with "L", using "^" to match the beginning of the name and "[LL]" to match the lowercase or uppercase "L"

SELECT * from Persons where City REGEXP "^[ll]";

Query LastName The person ending with "sh", using "$" to match the end of the name:

SELECT * from Persons WHERE LastName REGEXP "sh$";

Query LastName that contains a "a", use "[AA]" to match lowercase or uppercase "a":

SELECT * from Persons WHERE LastName REGEXP "[AA]";

Query the city name for a record that contains exactly 6 characters, using "^" and "$" to match the start and end of the name, and 6 "." The instance is between the two:

SELECT * from Persons WHERE city REGEXP "^......$";

You can also use the "{n}" "Repeat N-times" operator:

SELECT * FROM pet WHERE name REGEXP "^. {6}$ ";

This article is from the "Datas" blog, make sure to keep this source http://datas.blog.51cto.com/10590222/1894921

Database MySQL wildcard character and pattern matching regular expression