A summary of MySQL wildcard learning

MySQL wildcard characters

The pattern match for SQL agrees that you use "_" to match any single character, while "%" matches the random number of characters (containing 0 characters). In MySQL, the default mode of SQL is to ignore uppercase and lowercase. Some examples are shown below.

Note that when you use SQL mode, you cannot use = or! =, and use the like or not as a comparison operator.

In order to find the name starting with "B":

Mysql> select * FROM pet WHEREname "b%";
+--------+--------+---------+------+------------+------------+
| name | Owner | Species | sex | Birth | Death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | Dog | f | 1989-05-13 | NULL |
| Bowser | Diane | Dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

In order to find the name ending with "FY":

Mysql> select * FROM pet WHEREname "%fy";
+--------+--------+---------+------+------------+-------+
| name | Owner | Species | sex | Birth | Death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | Cat | f | 1993-02-04 | NULL |
| Buffy | Harold | Dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+

In order to find out the name including a "W":

Mysql> select * FROM pet WHEREname "%w%";
+----------+-------+---------+------+------------+------------+
| name | Owner | Species | sex | Birth | Death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | Cat | m | 1994-03-17 | NULL |
| Bowser | Diane | Dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | Bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+

To find a name that includes exactly 5 characters, use the "_" mode character:

Mysql> select * FROM pet WHEREname is like "_____";
+-------+--------+---------+------+------------+-------+
| name | Owner | Species | sex | Birth | Death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | Cat | m | 1994-03-17 | NULL |
| Buffy | Harold | Dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+

The other types of patterns that are provided by MySQL are the use of extended regular expressions. When you match this type of pattern, use the regexp and not regexp operators (or rlike and not rlike, which are synonyms).

Some of the characters that extend the normal expression are:

“.” Matches no matter what the individual characters are.
A character class "[...]" Matches whatever character is inside the square brackets. For example, "[ABC]" matches "a", "B" or "C". To name a range of characters, use a "-". "[A-Z]" matches no matter what lowercase letters, and "[0-9]" matches no matter what number.
" * " matches 0 or more things in front of it. For example, "x*" matches no matter what number of "X" characters, "[0-9]*" matches whatever number of numbers, while ". *" matches no matter what number of whatever.
The normal form is a distinction between uppercase and lowercase, but suppose you want to be able to use a character class to match two types of notation. For example, "[AA]" matches lowercase or uppercase "a" and "[A-za-z]" matches either letter.

Let's say the pattern is matched wherever it is now measured (just to match the entire value, SQL pattern match).
To locate a pattern so that it must match the start or end of the measured value, use "^" at the beginning of the pattern or "$" at the end of the pattern.
To illustrate how the extended normal table works, the like query you see above is rewritten with the following using regexp:

To find the name starting with "B", use "^" to match the beginning of the name and "[BB]" to match the lowercase or uppercase "B":

Mysql> select * FROM pet WHEREname REGEXP "^[BB]";
+--------+--------+---------+------+------------+------------+
| name | Owner | Species | sex | Birth | Death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | Dog | f | 1989-05-13 | NULL |
| Bowser | Diane | Dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

To find the name ending with "FY", use "$" to match the end of the name:

Mysql> select * FROM pet WHEREname REGEXP "fy$";
+--------+--------+---------+------+------------+-------+
| name | Owner | Species | sex | Birth | Death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | Cat | f | 1993-02-04 | NULL |
| Buffy | Harold | Dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+

To find the name that includes a "w", use "[WW]" to match lowercase or uppercase "W":

Mysql> select * FROM pet WHEREname REGEXP "[WW]";
+----------+-------+---------+------+------------+------------+
| name | Owner | Species | sex | Birth | Death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | Cat | m | 1994-03-17 | NULL |
| Bowser | Diane | Dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | Bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+

Now that a formal expression is assumed to be of any place in the current value, its pattern matches, it is not necessary to place a wildcard character in the previous query in the double aspect of the pattern so that it matches the entire value, as if you had used a SQL schema.

To find a name that includes exactly 5 characters, use "^" and "$" to match the beginning and end of the name, and 5 "." The instance is between the two:

Mysql> select * FROM pet WHEREname REGEXP "^.....$";
+-------+--------+---------+------+------------+-------+
| name | Owner | Species | sex | Birth | Death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | Cat | m | 1994-03-17 | NULL |
| Buffy | Harold | Dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+

You can also rewrite the previous query using the "{n}" "N-Times" operator:

Mysql> select * FROM pet WHEREname REGEXP "^.{ 5}$";
+-------+--------+---------+------+------------+-------+
| name | Owner | Species | sex | Birth | Death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | Cat | m | 1994-03-17 | NULL |
| Buffy | Harold | Dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+

http://www.playhosts.com/bbs/read.php?tid=12357

=====================================================================

A summary of MySQL wildcard learning