MySQL wildcard Learning Summary

MySQL wildcard Learning Summary MySQL wildcard

SQL pattern matching allows you to use "_" to match a single character, while "%" matches a random number of characters (including zero characters ). In MySQL, the SQL Mode ignores uppercase and lowercase letters by default. The following are some examples.

Note that when you use SQL Mode, you cannot use = OR! =; The LIKE or not like operator is used.

To find the name starting with "B:

Mysql> SELECT * FROM pet WHEREnameLIKE"

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 |

+ -------- + --------- + ------ + ------------ +

To find the name ending with "fy:

Mysql> SELECT * FROM pet WHEREnameLIKE "% 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 a name that includes "w:

Mysql> SELECT * FROM pet WHEREnameLIKE "% 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 locate the name that contains exactly five characters, use the "_" pattern character:

Mysql> SELECT * FROM pet WHEREnameLIKE "_____";

+ ------- + -------- + --------- + ------ + ------------ + ------- +

| Name | owner | species | sex | birth | death |

+ ------- + -------- + --------- + ------ + ------------ + ------- +

|

Claws

| Gwen | cat | m | 1994-03-17 | NULL |

|

Buffy

| Harold | dog | f | 1989-05-13 | NULL |

+ ------- + -------- + --------- + ------ + ------------ + ------- +

Another type of pattern matching provided by MySQL is the extended regular expression. When you try to match this type of pattern, use the REGEXP and not regexp operators (or RLIKE and not rlike, they are synonyms ).

Some characters of the extended regular expression are:

"." Matches any single character.

A character class "[...]" matches any character in square brackets. For example, "[abc]" matches "a", "B", or "c ". To name a range of characters, use a hyphen (-). "[A-z

]

"Match no matter what lowercase letters, and" [0-9] "match no matter what number.

"

*

"Matches zero or multiple items before it. For example, "x *" matches no matter what number of "x" characters, "[0-9] *" matches no matter what number of numbers, and ". * "matches whatever number of items.

The regular expression of the table distinguishes uppercase and lowercase letters, but if you want to, you can use one character class matching statement. For example, "[aA]" matches lowercase or upper-case "a", and "[a-zA-Z]" matches any letter in either of the two statements.

Suppose that the pattern matches wherever the value is being tested (only requires them to match the entire value, and the SQL pattern matches ).

In order to locate a pattern so that it must match the start or end of the tested value, use "^" at the beginning of the pattern or "$" at the end of the pattern ".

To illustrate how the regular expression of the extended regular expression works, the LIKE query shown above is rewritten using REGEXP below:

To find the name starting with "B", use "^" to match the start of the name and use "[bB]" to match "B" in lower case or upper case ":

Mysql> SELECT * FROM pet WHEREnameREGEXP "^ [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 WHEREnameREGEXP "fy $ ";

+ -------- + --------- + ------ + ------------ + ------- +

| Name | owner | species | sex | birth | death |

+ -------- + --------- + ------ + ------------ + ------- +

|

Fluffy

| Harold | cat | f | 1993-02-04 | NULL |

|

Buffy

| Harold | dog | f | 1989-05-13 | NULL |

+ -------- + --------- + ------ + ------------ + ------- +

To locate a name that includes "w", use "[wW]" to match "w" in lower or upper case ":

Mysql> SELECT * FROM pet WHEREnameREGEXP "[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 |

+ ---------- + ------- + --------- + ------ + ------------ +

Assume that a regular expression has a pattern match wherever the current value exists, so you do not have to place a wildcard in the two aspects of the pattern in the previous query to make it match the entire value, just as if you use an SQL mode.

To locate a name that contains exactly five characters, use "^" and "$" to match the start and end of the name, and the five "." instances are in the same order:

Mysql> SELECT * FROM pet WHEREnameREGEXP "^... $ ";

+ ------- + -------- + --------- + ------ + ------------ + ------- +

| 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 use the "{n}" "repeated n times" operator to rewrite the previous query:

Mysql> SELECT * FROM pet WHEREnameREGEXP "^. {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

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