Rechercher dans le manuel MySQL

3.3.4.7 Pattern Matching

Inhaltsverzeichnis

MySQL provides standard SQL pattern matching as well as a form of pattern matching based on extended regular expressions similar to those used by Unix utilities such as vi, grep, and sed.

SQL pattern matching enables you to use _ to match any single character and % to match an arbitrary number of characters (including zero characters). In MySQL, SQL patterns are case-insensitive by default. Some examples are shown here. Do not use = or <> when you use SQL patterns. Use the LIKE or NOT LIKE comparison operators instead.

To find names beginning with b:

  1. mysql> SELECT * FROM pet WHERE name LIKE 'b%';
  2. +--------+--------+---------+------+------------+------------+
  3. | name   | owner  | species | sex  | birth      | death      |
  4. +--------+--------+---------+------+------------+------------+
  5. | Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
  6. | Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
  7. +--------+--------+---------+------+------------+------------+

To find names ending with fy:

  1. mysql> SELECT * FROM pet WHERE name LIKE '%fy';
  2. +--------+--------+---------+------+------------+-------+
  3. | name   | owner  | species | sex  | birth      | death |
  4. +--------+--------+---------+------+------------+-------+
  5. | Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
  6. | Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
  7. +--------+--------+---------+------+------------+-------+

To find names containing a w:

  1. mysql> SELECT * FROM pet WHERE name LIKE '%w%';
  2. +----------+-------+---------+------+------------+------------+
  3. | name     | owner | species | sex  | birth      | death      |
  4. +----------+-------+---------+------+------------+------------+
  5. | Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
  6. | Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
  7. | Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
  8. +----------+-------+---------+------+------------+------------+

To find names containing exactly five characters, use five instances of the _ pattern character:

  1. mysql> SELECT * FROM pet WHERE name LIKE '_____';
  2. +-------+--------+---------+------+------------+-------+
  3. | name  | owner  | species | sex  | birth      | death |
  4. +-------+--------+---------+------+------------+-------+
  5. | Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
  6. | Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
  7. +-------+--------+---------+------+------------+-------+

The other type of pattern matching provided by MySQL uses extended regular expressions. When you test for a match for this type of pattern, use the REGEXP_LIKE() function (or the REGEXP or RLIKE operators, which are synonyms for REGEXP_LIKE()).

The following list describes some characteristics of extended regular expressions:

  • . matches any single character.

  • A character class [...] matches any character within the brackets. For example, [abc] matches a, b, or c. To name a range of characters, use a dash. [a-z] matches any letter, whereas [0-9] matches any digit.

  • * matches zero or more instances of the thing preceding it. For example, x* matches any number of x characters, [0-9]* matches any number of digits, and .* matches any number of anything.

  • A regular expression pattern match succeeds if the pattern matches anywhere in the value being tested. (This differs from a LIKE pattern match, which succeeds only if the pattern matches the entire value.)

  • To anchor a pattern so that it must match the beginning or end of the value being tested, use ^ at the beginning or $ at the end of the pattern.

To demonstrate how extended regular expressions work, the LIKE queries shown previously are rewritten here to use REGEXP_LIKE().

To find names beginning with b, use ^ to match the beginning of the name:

  1. mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b');
  2. +--------+--------+---------+------+------------+------------+
  3. | name   | owner  | species | sex  | birth      | death      |
  4. +--------+--------+---------+------+------------+------------+
  5. | Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
  6. | Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
  7. +--------+--------+---------+------+------------+------------+

To force a regular expression comparison to be case sensitive, use a case-sensitive collation, or use the BINARY keyword to make one of the strings a binary string, or specify the c match-control character. Each of these queries matches only lowercase b at the beginning of a name:

  1. SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b' COLLATE utf8mb4_0900_as_cs);
  2. SELECT * FROM pet WHERE REGEXP_LIKE(name, BINARY '^b');
  3. SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b', 'c');

To find names ending with fy, use $ to match the end of the name:

  1. mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'fy$');
  2. +--------+--------+---------+------+------------+-------+
  3. | name   | owner  | species | sex  | birth      | death |
  4. +--------+--------+---------+------+------------+-------+
  5. | Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
  6. | Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
  7. +--------+--------+---------+------+------------+-------+

To find names containing a w, use this query:

  1. mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'w');
  2. +----------+-------+---------+------+------------+------------+
  3. | name     | owner | species | sex  | birth      | death      |
  4. +----------+-------+---------+------+------------+------------+
  5. | Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
  6. | Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
  7. | Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
  8. +----------+-------+---------+------+------------+------------+

Because a regular expression pattern matches if it occurs anywhere in the value, it is not necessary in the previous query to put a wildcard on either side of the pattern to get it to match the entire value as would be true with an SQL pattern.

To find names containing exactly five characters, use ^ and $ to match the beginning and end of the name, and five instances of . in between:

  1. mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$');
  2. +-------+--------+---------+------+------------+-------+
  3. | name  | owner  | species | sex  | birth      | death |
  4. +-------+--------+---------+------+------------+-------+
  5. | Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
  6. | Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
  7. +-------+--------+---------+------+------------+-------+

You could also write the previous query using the {n} (“repeat-n-times”) operator:

  1. mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$');
  2. +-------+--------+---------+------+------------+-------+
  3. | name  | owner  | species | sex  | birth      | death |
  4. +-------+--------+---------+------+------------+-------+
  5. | Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
  6. | Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
  7. +-------+--------+---------+------+------------+-------+

For more information about the syntax for regular expressions, see Section 12.5.2, “Regular Expressions”.


Suchen Sie im MySQL-Handbuch

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-pattern-matching.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.

Referenzen

  1. Zeigen Sie - html-Dokument Sprache des Dokuments:en Manuel MySQL : https://dev.mysql.com/

Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.

Inhaltsverzeichnis Haut