What is the MySQL REGEXP operator and how does it handle pattern matching?

#MySQL supports an alternative pattern matching operation based on regular expressions and the REGEXP operator. The following is a table of patterns that can be used with the REGEXP operator to handle pattern matching.

tbody>

Pattern	What does the pattern match
^	Start of string
$ p>	End of string
.	Any single character
[...]	Any characters listed between square brackets
[^...]	Any characters not listed within square brackets
p1|p2|p3	Alternate; match any pattern p1, p2, or p3
*	Zero or more instances of the preceding element
	One or more instances of the previous element
{n}	before n instances of an element
{m,n}	m to n instances of the previous element

##Example

To illustrate the use of REGEXP, we use the table "Student_info" with the following data -

mysql> Select * from Student_info;
+------+---------+------------+------------+
| id   | Name    | Address    | Subject    |
+------+---------+------------+------------+
|  101 | YashPal | Amritsar   | History    |
|  105 | Gaurav  | Chandigarh | Literature |
|  130 | Ram     | Jhansi     | Computers  |
|  132 | Shyam   | Chandigarh | Economics  |
|  133 | Mohan   | Delhi      | Computers  |
+------+---------+------------+------------+
5 rows in set (0.00 sec)

Now, here are some queries to find the "Name" pattern from the above table using REGEXP -

mysql> Select Name from student_info WHERE Name REGEXP '^Y';
+---------+
| Name    |
+---------+
| YashPal |
+---------+
1 row in set (0.11 sec)

The above query will find all the names starting with "Y".

mysql> Select name from student_info WHERE Name REGEXP 'am$';
+-------+
| name  |
+-------+
| Ram   |
| Shyam |
+-------+
2 rows in set (0.00 sec)

The above query will find all names ending with "am".

mysql> Select name from student_info WHERE Name REGEXP 'av';
+--------+
| name   |
+--------+
| Gaurav |
+--------+
1 row in set (0.00 sec)

The above query will find all names containing "av".

mysql> Select name from student_info WHERE Name REGEXP '^[aeiou]|am$';
+-------+
| name  |
+-------+
| Ram   |
| Shyam |
+-------+
2 rows in set (0.00 sec)

The above query will find all names starting with a vowel and ending with "am".

The above is the detailed content of What is the MySQL REGEXP operator and how does it handle pattern matching?. For more information, please follow other related articles on the PHP Chinese website!