MySQL Tutorial/Regular Expressions/REGEXP

Содержание

1 Characters and classes used by the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms)
2 REGEXP "^[a-f]"
3 REGEXP "^[mn].*[sz]$"
4 SELECT "1+2" REGEXP "1\\+2";
5 To find names beginning with "J", use "^" to match the beginning of the name
6 To find names containing an "e", use this query
7 To find names containing exactly five characters
8 To find names containing exactly five characters using {n}
9 To find names ending with "es", use "$" to match the end of the name
10 To matches only lowercase "b" at the beginning of a name

Characters and classes used by the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms)

"." 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.

".*" matches any number of anything.

A REGEXP pattern match succeeds if the pattern matches anywhere in the value being tested.

A LIKE pattern match succeeds only if the pattern matches the entire value.

"^" matches the beginning of the pattern.

"$" matches the end of the pattern.

24. 10. REGEXP 24. 10. 1. Characters and classes used by the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms) 24. 10. 2. <A href="/Tutorial/MySQL/0480__Regular-Expressions/TofindnamesbeginningwithJusetomatchthebeginningofthename.htm">To find names beginning with "J", use "^" to match the beginning of the name</a> 24. 10. 3. <A href="/Tutorial/MySQL/0480__Regular-Expressions/Tomatchesonlylowercasebatthebeginningofaname.htm">To matches only lowercase "b" at the beginning of a name</a> 24. 10. 4. <A href="/Tutorial/MySQL/0480__Regular-Expressions/Tofindnamesendingwithesusetomatchtheendofthename.htm">To find names ending with "es", use "$" to match the end of the name</a> 24. 10. 5. <A href="/Tutorial/MySQL/0480__Regular-Expressions/Tofindnamescontaininganeusethisquery.htm">To find names containing an "e", use this query</a> 24. 10. 6. <A href="/Tutorial/MySQL/0480__Regular-Expressions/Tofindnamescontainingexactlyfivecharacters.htm">To find names containing exactly five characters</a> 24. 10. 7. <A href="/Tutorial/MySQL/0480__Regular-Expressions/Tofindnamescontainingexactlyfivecharactersusingn.htm">To find names containing exactly five characters using {n}</a> 24. 10. 8. <A href="/Tutorial/MySQL/0480__Regular-Expressions/SELECT12REGEXP12.htm">SELECT "1+2" REGEXP "1\\+2";</a> 24. 10. 9. <A href="/Tutorial/MySQL/0480__Regular-Expressions/REGEXPaf.htm">REGEXP "^[a-f]"</a> 24. 10. 10. <A href="/Tutorial/MySQL/0480__Regular-Expressions/REGEXPmnsz.htm">REGEXP "^[mn].*[sz]$"</a>

REGEXP "^[a-f]"

mysql>
mysql>
mysql> CREATE TABLE Topic(
    ->    TopicID     SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    Name        VARCHAR(50) NOT NULL,
    ->    InStock     SMALLINT UNSIGNED NOT NULL,
    ->    OnOrder     SMALLINT UNSIGNED NOT NULL,
    ->    Reserved    SMALLINT UNSIGNED NOT NULL,
    ->    Department  ENUM("Classical", "Popular") NOT NULL,
    ->    Category    VARCHAR(20) NOT NULL,
    ->    RowUpdate   TIMESTAMP NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql>
mysql> INSERT INTO Topic (Name,          InStock, OnOrder, Reserved, Department,   Category) VALUES
    ->                   ("Java",          10,      5,       3,        "Popular",    "Rock"),
    ->                   ("JavaScript",    10,      5,       3,        "Classical",  "Opera"),
    ->                   ("C Sharp",       17,      4,       1,        "Popular",    "Jazz"),
    ->                   ("C",             9,       4,       2,        "Classical",  "Dance"),
    ->                   ("C++",           24,      2,       5,        "Classical",  "General"),
    ->                   ("Perl",          16,      6,       8,        "Classical",  "Vocal"),
    ->                   ("Python",        2,       25,      6,        "Popular",    "Blues"),
    ->                   ("Php",           32,      3,       10,       "Popular",    "Jazz"),
    ->                   ("ASP.net",       12,      15,      13,       "Popular",    "Country"),
    ->                   ("VB.net",        5,       20,      10,       "Popular",    "New Age"),
    ->                   ("VC.net",        24,      11,      14,       "Popular",    "New Age"),
    ->                   ("UML",           42,      17,      17,       "Classical",  "General"),
    ->                   ("www.sqle.ru",25,      44,      28,       "Classical",  "Dance"),
    ->                   ("Oracle",        32,      15,      12,       "Classical",  "General"),
    ->                   ("Pl/SQL",        20,      10,      5,        "Classical",  "Opera"),
    ->                   ("Sql Server",    23,      12,      8,        "Classical",  "General");
Query OK, 16 rows affected (0.02 sec)
Records: 16  Duplicates: 0  Warnings: 0
mysql>
mysql> select * from Topic;
+---------+----------------+---------+---------+----------+------------+----------+---------------------+
| TopicID | Name           | InStock | OnOrder | Reserved | Department | Category | RowUpdate           |
+---------+----------------+---------+---------+----------+------------+----------+---------------------+
|       1 | Java           |      10 |       5 |        3 | Popular    | Rock     | 2007-07-23 19:09:49 |
|       2 | JavaScript     |      10 |       5 |        3 | Classical  | Opera    | 2007-07-23 19:09:49 |
|       3 | C Sharp        |      17 |       4 |        1 | Popular    | Jazz     | 2007-07-23 19:09:49 |
|       4 | C              |       9 |       4 |        2 | Classical  | Dance    | 2007-07-23 19:09:49 |
|       5 | C++            |      24 |       2 |        5 | Classical  | General  | 2007-07-23 19:09:49 |
|       6 | Perl           |      16 |       6 |        8 | Classical  | Vocal    | 2007-07-23 19:09:49 |
|       7 | Python         |       2 |      25 |        6 | Popular    | Blues    | 2007-07-23 19:09:49 |
|       8 | Php            |      32 |       3 |       10 | Popular    | Jazz     | 2007-07-23 19:09:49 |
|       9 | ASP.net        |      12 |      15 |       13 | Popular    | Country  | 2007-07-23 19:09:49 |
|      10 | VB.net         |       5 |      20 |       10 | Popular    | New Age  | 2007-07-23 19:09:49 |
|      11 | VC.net         |      24 |      11 |       14 | Popular    | New Age  | 2007-07-23 19:09:49 |
|      12 | UML            |      42 |      17 |       17 | Classical  | General  | 2007-07-23 19:09:49 |
|      13 | www.sqle.ru |      25 |      44 |       28 | Classical  | Dance    | 2007-07-23 19:09:49 |
|      14 | Oracle         |      32 |      15 |       12 | Classical  | General  | 2007-07-23 19:09:49 |
|      15 | Pl/SQL         |      20 |      10 |        5 | Classical  | Opera    | 2007-07-23 19:09:49 |
|      16 | Sql Server     |      23 |      12 |        8 | Classical  | General  | 2007-07-23 19:09:49 |
+---------+----------------+---------+---------+----------+------------+----------+---------------------+
16 rows in set (0.00 sec)
mysql>
mysql>
mysql> SELECT Name, InStock+OnOrder-Reserved AS Available
    -> FROM Topic
    -> WHERE Name REGEXP "^[a-f]"
    -> ORDER BY Name;
+---------+-----------+
| Name    | Available |
+---------+-----------+
| ASP.net |        14 |
| C       |        11 |
| C Sharp |        20 |
| C++     |        21 |
+---------+-----------+
4 rows in set (0.00 sec)
mysql>
mysql> drop table Topic;
Query OK, 0 rows affected (0.00 sec)

REGEXP "^[mn].*[sz]$"

mysql>
mysql>
mysql> CREATE TABLE Topic(
    ->    TopicID     SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    Name        VARCHAR(50) NOT NULL,
    ->    InStock     SMALLINT UNSIGNED NOT NULL,
    ->    OnOrder     SMALLINT UNSIGNED NOT NULL,
    ->    Reserved    SMALLINT UNSIGNED NOT NULL,
    ->    Department  ENUM("Classical", "Popular") NOT NULL,
    ->    Category    VARCHAR(20) NOT NULL,
    ->    RowUpdate   TIMESTAMP NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>
mysql> INSERT INTO Topic (Name,          InStock, OnOrder, Reserved, Department,   Category) VALUES
    ->                   ("Java",          10,      5,       3,        "Popular",    "Rock"),
    ->                   ("JavaScript",    10,      5,       3,        "Classical",  "Opera"),
    ->                   ("C Sharp",       17,      4,       1,        "Popular",    "Jazz"),
    ->                   ("C",             9,       4,       2,        "Classical",  "Dance"),
    ->                   ("C++",           24,      2,       5,        "Classical",  "General"),
    ->                   ("Perl",          16,      6,       8,        "Classical",  "Vocal"),
    ->                   ("Python",        2,       25,      6,        "Popular",    "Blues"),
    ->                   ("Php",           32,      3,       10,       "Popular",    "Jazz"),
    ->                   ("ASP.net",       12,      15,      13,       "Popular",    "Country"),
    ->                   ("VB.net",        5,       20,      10,       "Popular",    "New Age"),
    ->                   ("VC.net",        24,      11,      14,       "Popular",    "New Age"),
    ->                   ("UML",           42,      17,      17,       "Classical",  "General"),
    ->                   ("www.sqle.ru",25,      44,      28,       "Classical",  "Dance"),
    ->                   ("Oracle",        32,      15,      12,       "Classical",  "General"),
    ->                   ("Pl/SQL",        20,      10,      5,        "Classical",  "Opera"),
    ->                   ("Sql Server",    23,      12,      8,        "Classical",  "General");
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0
mysql>
mysql> select * from Topic;
+---------+----------------+---------+---------+----------+------------+----------+---------------------+
| TopicID | Name           | InStock | OnOrder | Reserved | Department | Category | RowUpdate           |
+---------+----------------+---------+---------+----------+------------+----------+---------------------+
|       1 | Java           |      10 |       5 |        3 | Popular    | Rock     | 2007-07-23 19:09:49 |
|       2 | JavaScript     |      10 |       5 |        3 | Classical  | Opera    | 2007-07-23 19:09:49 |
|       3 | C Sharp        |      17 |       4 |        1 | Popular    | Jazz     | 2007-07-23 19:09:49 |
|       4 | C              |       9 |       4 |        2 | Classical  | Dance    | 2007-07-23 19:09:49 |
|       5 | C++            |      24 |       2 |        5 | Classical  | General  | 2007-07-23 19:09:49 |
|       6 | Perl           |      16 |       6 |        8 | Classical  | Vocal    | 2007-07-23 19:09:49 |
|       7 | Python         |       2 |      25 |        6 | Popular    | Blues    | 2007-07-23 19:09:49 |
|       8 | Php            |      32 |       3 |       10 | Popular    | Jazz     | 2007-07-23 19:09:49 |
|       9 | ASP.net        |      12 |      15 |       13 | Popular    | Country  | 2007-07-23 19:09:49 |
|      10 | VB.net         |       5 |      20 |       10 | Popular    | New Age  | 2007-07-23 19:09:49 |
|      11 | VC.net         |      24 |      11 |       14 | Popular    | New Age  | 2007-07-23 19:09:49 |
|      12 | UML            |      42 |      17 |       17 | Classical  | General  | 2007-07-23 19:09:49 |
|      13 | www.sqle.ru |      25 |      44 |       28 | Classical  | Dance    | 2007-07-23 19:09:49 |
|      14 | Oracle         |      32 |      15 |       12 | Classical  | General  | 2007-07-23 19:09:49 |
|      15 | Pl/SQL         |      20 |      10 |        5 | Classical  | Opera    | 2007-07-23 19:09:49 |
|      16 | Sql Server     |      23 |      12 |        8 | Classical  | General  | 2007-07-23 19:09:49 |
+---------+----------------+---------+---------+----------+------------+----------+---------------------+
16 rows in set (0.00 sec)
mysql>
mysql>
mysql> SELECT Name, InStock
    -> FROM Topic
    -> WHERE Name REGEXP "^[mn].*[sz]$"
    -> ORDER BY Name;
Empty set (0.00 sec)
mysql>
mysql> drop table Topic;
Query OK, 0 rows affected (0.00 sec)

SELECT "1+2" REGEXP "1\\+2";

mysql>
mysql>
mysql>
mysql> SELECT "1+2" REGEXP "1+2";
+--------------------+
| "1+2" REGEXP "1+2" |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT "1+2" REGEXP "1\+2";
+---------------------+
| "1+2" REGEXP "1\+2" |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT "1+2" REGEXP "1\\+2";
+----------------------+
| "1+2" REGEXP "1\\+2" |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)
mysql>

To find names beginning with "J", use "^" to match the beginning of the name

mysql>
mysql>
mysql> CREATE TABLE Employee(
    ->     id            int,
    ->     first_name    VARCHAR(15),
    ->     last_name     VARCHAR(15),
    ->     start_date    DATE,
    ->     end_date      DATE,
    ->     salary        FLOAT(8,2),
    ->     city          VARCHAR(10),
    ->     description   VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->              values (1,"Jason",    "Martin",  "19960725",  "20060725", 1234.56, "Toronto",  "Programmer");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(2,"Alison",   "Mathews",  "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(3,"James",    "Smith",    "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(4,"Celia",    "Rice",     "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(5,"Robert",   "Black",    "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(6,"Linda",    "Green",    "19870730", "19960104", 4322.78,"New York",  "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(7,"David",    "Larry",    "19901231", "19980212", 7897.78,"New York",  "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(8,"James",    "Cat",     "19960917",  "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer  |
|    2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester      |
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
|    5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester      |
|    6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4322.78 | New York  | Tester      |
|    7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7897.78 | New York  | Manager     |
|    8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql>
mysql> SELECT * FROM employee WHERE first_name REGEXP "^J";
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer  |
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
3 rows in set (0.02 sec)
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql>

To find names containing an "e", use this query

mysql>
mysql>
mysql> CREATE TABLE Employee(
    ->     id            int,
    ->     first_name    VARCHAR(15),
    ->     last_name     VARCHAR(15),
    ->     start_date    DATE,
    ->     end_date      DATE,
    ->     salary        FLOAT(8,2),
    ->     city          VARCHAR(10),
    ->     description   VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->              values (1,"Jason",    "Martin",  "19960725",  "20060725", 1234.56, "Toronto",  "Programmer");
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(2,"Alison",   "Mathews",  "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(3,"James",    "Smith",    "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(4,"Celia",    "Rice",     "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(5,"Robert",   "Black",    "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(6,"Linda",    "Green",    "19870730", "19960104", 4322.78,"New York",  "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(7,"David",    "Larry",    "19901231", "19980212", 7897.78,"New York",  "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(8,"James",    "Cat",     "19960917",  "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer  |
|    2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester      |
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
|    5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester      |
|    6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4322.78 | New York  | Tester      |
|    7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7897.78 | New York  | Manager     |
|    8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> SELECT * FROM employee WHERE first_name REGEXP "e";
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
|    5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester      |
|    8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
4 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.01 sec)
mysql>

To find names containing exactly five characters

Use "^" and "$" to match the beginning and end of the name.

Use five instances of "." in between.

mysql>
mysql>
mysql>
mysql>
mysql> CREATE TABLE Employee(
    ->     id            int,
    ->     first_name    VARCHAR(15),
    ->     last_name     VARCHAR(15),
    ->     start_date    DATE,
    ->     end_date      DATE,
    ->     salary        FLOAT(8,2),
    ->     city          VARCHAR(10),
    ->     description   VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->              values (1,"Jason",    "Martin",  "19960725",  "20060725", 1234.56, "Toronto",  "Programmer");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(2,"Alison",   "Mathews",  "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(3,"James",    "Smith",    "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(4,"Celia",    "Rice",     "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(5,"Robert",   "Black",    "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(6,"Linda",    "Green",    "19870730", "19960104", 4322.78,"New York",  "Tester");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(7,"David",    "Larry",    "19901231", "19980212", 7897.78,"New York",  "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(8,"James",    "Cat",     "19960917",  "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer  |
|    2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester      |
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
|    5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester      |
|    6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4322.78 | New York  | Tester      |
|    7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7897.78 | New York  | Manager     |
|    8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> SELECT * FROM employee WHERE first_name REGEXP "^.....$";
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer  |
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
|    6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4322.78 | New York  | Tester      |
|    7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7897.78 | New York  | Manager     |
|    8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
6 rows in set (0.00 sec)
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql>

To find names containing exactly five characters using {n}

mysql>
mysql>
mysql> CREATE TABLE Employee(
    ->     id            int,
    ->     first_name    VARCHAR(15),
    ->     last_name     VARCHAR(15),
    ->     start_date    DATE,
    ->     end_date      DATE,
    ->     salary        FLOAT(8,2),
    ->     city          VARCHAR(10),
    ->     description   VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->              values (1,"Jason",    "Martin",  "19960725",  "20060725", 1234.56, "Toronto",  "Programmer");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(2,"Alison",   "Mathews",  "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(3,"James",    "Smith",    "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(4,"Celia",    "Rice",     "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(5,"Robert",   "Black",    "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(6,"Linda",    "Green",    "19870730", "19960104", 4322.78,"New York",  "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(7,"David",    "Larry",    "19901231", "19980212", 7897.78,"New York",  "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(8,"James",    "Cat",     "19960917",  "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer  |
|    2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester      |
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
|    5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester      |
|    6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4322.78 | New York  | Tester      |
|    7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7897.78 | New York  | Manager     |
|    8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> SELECT * FROM employee WHERE first_name REGEXP "^.{5}$";
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer  |
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
|    6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4322.78 | New York  | Tester      |
|    7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7897.78 | New York  | Manager     |
|    8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
6 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql>
mysql>

To find names ending with "es", use "$" to match the end of the name

mysql>
mysql>
mysql> CREATE TABLE Employee(
    ->     id            int,
    ->     first_name    VARCHAR(15),
    ->     last_name     VARCHAR(15),
    ->     start_date    DATE,
    ->     end_date      DATE,
    ->     salary        FLOAT(8,2),
    ->     city          VARCHAR(10),
    ->     description   VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->              values (1,"Jason",    "Martin",  "19960725",  "20060725", 1234.56, "Toronto",  "Programmer");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(2,"Alison",   "Mathews",  "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(3,"James",    "Smith",    "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(4,"Celia",    "Rice",     "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(5,"bobert",   "Black",    "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(6,"Linda",    "Green",    "19870730", "19960104", 4322.78,"New York",  "Tester");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(7,"David",    "Larry",    "19901231", "19980212", 7897.78,"New York",  "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(8,"bames",    "Cat",     "19960917",  "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer  |
|    2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester      |
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
|    5 | bobert     | Black     | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester      |
|    6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4322.78 | New York  | Tester      |
|    7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7897.78 | New York  | Manager     |
|    8 | bames      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> SELECT * FROM employee WHERE first_name REGEXP "es$";
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    8 | bames      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
2 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>

To matches only lowercase "b" at the beginning of a name

mysql>
mysql>
mysql>
mysql> CREATE TABLE Employee(
    ->     id            int,
    ->     first_name    VARCHAR(15),
    ->     last_name     VARCHAR(15),
    ->     start_date    DATE,
    ->     end_date      DATE,
    ->     salary        FLOAT(8,2),
    ->     city          VARCHAR(10),
    ->     description   VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->              values (1,"Jason",    "Martin",  "19960725",  "20060725", 1234.56, "Toronto",  "Programmer");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(2,"Alison",   "Mathews",  "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(3,"James",    "Smith",    "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(4,"Celia",    "Rice",     "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(5,"bobert",   "Black",    "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(6,"Linda",    "Green",    "19870730", "19960104", 4322.78,"New York",  "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(7,"David",    "Larry",    "19901231", "19980212", 7897.78,"New York",  "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(8,"bames",    "Cat",     "19960917",  "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer  |
|    2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester      |
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
|    5 | bobert     | Black     | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester      |
|    6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4322.78 | New York  | Tester      |
|    7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7897.78 | New York  | Manager     |
|    8 | bames      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> SELECT * FROM employee WHERE first_name REGEXP BINARY "^b";
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    5 | bobert     | Black     | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester      |
|    8 | bames      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
2 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>

MySQL Tutorial/Regular Expressions/REGEXP

Содержание

Characters and classes used by the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms)

REGEXP "^[a-f]"

REGEXP "^[mn].*[sz]$"

SELECT "1+2" REGEXP "1\\+2";

To find names beginning with "J", use "^" to match the beginning of the name

To find names containing an "e", use this query

To find names containing exactly five characters

To find names containing exactly five characters using {n}

To find names ending with "es", use "$" to match the end of the name

To matches only lowercase "b" at the beginning of a name

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты