MySQL Wildcard Learning Summary

MySQL wildcard characters

SQL you agree to use pattern matching "_" regardless of the individual characters that match, and "%" matches the random number of characters (containing 0 characters).

In MySQL. The default mode for SQL is to ignore uppercase and lowercase.

Some examples are shown below.

Note 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 |
+----------+-------+---------+------+------------+------------+

In order 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 pattern, use the regexp and not regexp operators (or Rlike and not rlike. They 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 whatever number of "X" characters. "[0-9]*" matches no matter what number of numbers, and ". *" matches no matter what the number of whatever.
The normal form is a distinction between uppercase and lowercase, but suppose you want to. You can 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 extension of the normal form works. The like query you see above is overridden with regexp in the following:

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 the value of whatever place it is, its pattern matches. There is no need to place a wildcard character in the previous query on the two sides of the pattern so that it matches the entire value, as if you were using 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

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

MySQL Wildcard Learning Summary