Mysql (6) string pattern matching _ MySQL

Mysql (6) string pattern matching bitsCN.com

Mysql (6) string pattern matching

Related links:

Mysql (1) installation of mysql

Http: // database/201210/162314 .html;

Mysql-related operations (2)

Http: // database/201210/162315 .html;

Mysql operations on data tables (3)

Http: // database/201210/162316 .html;

Mysql (4) Data Table query operations

Http: // database/201210/162317 .html;

Mysql operations (5)

Http: // database/201210/162318 .html

I. matching the standard SQL Mode

In the matching mode of mysq, you can use "_" to match any single character and "%" to match any number of characters.

= OR! =, Use the LIKE or not like comparison operator.

Example:

1. find the name starting with "B" in the student table.

SELECT * FROM student WHERE name LIKE "B % ";

2. find the name ending with "B" in the student table.

SELECT * FROM student WHERE name LIKE "% B ";

3. find the name "B" in the student table.

SELECT * FROM student WHERE name LIKE "% B % ";

4. find the name exactly five characters in the student table.

SELECT * FROM student WHERE name LIKE "_"; (there are spaces to indicate that there are 5 "_")

II. Extended Regular expression pattern matching

When using regular expressions, you need to use other comparison operators, which are REGEXP or not regexp.

Extended Regular expression characters:

"." Matches any single character.

"*" Matches zero or multiple characters before it.

"X *" matches any number of X characters.

Regular expressions are case-sensitive and can be matched using a character class. [AA] matches lowercase and upper-case.

"^" Indicates the beginning and "$" indicates the end.

Example:

1. find the name starting with "B" in the student table.

SELECT * FROM student WHERE name REGEXP "^ [bB]";

2. find the name ending with "B" in the student table.

SELECT * FROM student WHERE name REGEXP "[bB] $ ";

3. find the name "B" in the student table.

SELECT * FROM student WHERE name REGEXP "[bB]";

4. find the name exactly five characters in the student table.

SELECT * FROM student WHERE name REGEXP "^ _ $"; (there are spaces to indicate that there are 5 "_")

BitsCN.com