Example analysis of the search function of MySQL regular expressions (regexp and rlike)

This article uses examples to illustrate the search function of MySQL regular expressions (regexp and rlike). Share with you for your reference, the details are as follows:

We know that regular expressions are special strings that describe search patterns. It is a powerful tool that provides us with a concise and flexible method to recognize pattern-based text characters such as characters, words, etc. For example, we can use regular expressions to search for emails, IP addresses, phone numbers, social security numbers, or anything with a specific pattern. Regular expressions have their own syntax that can be interpreted by a regular expression processor. Regular expressions are widely used in most platforms, from programming languages ​​to databases (including MySQL). The advantage of using regular expressions is that you are not limited to searching for strings based on fixed patterns with percent signs (%) and underscores (_) in the like operator. With regular expressions, there are more metacharacters to construct flexible patterns.

Knowing this, do we want to test the waters? ? ? Without further ado, MySQL allows you to use the REGEXP operator to match patterns in SQL statements, so let's take a look at the syntax format of regular expressions in the where clause:

SELECT 
 column_list
FROM
 table_name
WHERE
 string_column REGEXP pattern;

This SQL statement matches string_column with pattern. If the value in string_column matches pattern, the expression in the WHERE clause returns 1, otherwise it returns 0. If string_column or pattern is NULL, the result is NULL. In addition to the REGEXP operator, you can use the RLIKE operator, which is a synonym for the REGEXP operator. The negated form of the REGEXP operator is NOT REGEXP.

Now let's look at an example. Suppose we want to find products whose names start with the letter A, B, or C. You can use regular expressions in select statements as follows:

SELECT 
 productname
FROM
 products
WHERE
 productname REGEXP '^(A|B|C)'
ORDER BY productname;

Execute the above query statement and get the following results:

+--------------------------------+
| productname |
+--------------------------------+
| America West Airlines B757-200 |
| American Airlines: B767-300 |
| American Airlines: MD-11S |
| ATA: B757-300 |
| Boeing X-32A JSF |
| Collectable Wooden Train |
| Corsair F4U (Bird Cage) |
+--------------------------------+
7 rows in set

This pattern allows to search for products whose names start with A, B or C, where the character ^ means to match from the beginning of the string, and the ending character | searches for alternatives if no match is possible. The following table describes some commonly used metacharacters and constructs in regular expressions:

Metacharacters	Behavior
^	Matches a position at the beginning of the search string
$	Matches the end of the search string
.	Matches any single character
[…]	Matches any character within the square brackets
[^…]	Matches any character not specified within the square brackets
	Matches p1 or p2 pattern
*	Matches the preceding character zero or more times
+	Matches the previous character one or more times
{n}	Matches n instances of the first few characters
{m,n}	Matches from m to n instances of the previous character

Let's look for products whose names begin with a. We use "^" at the beginning of the name to match, as shown in the following query:

SELECT 
 productname
FROM
 products
WHERE
 productname REGEXP '^a';

Execute the above query statement and get the following results:

+--------------------------------+
| productname |
+--------------------------------+
| American Airlines: B767-300 |
| America West Airlines B757-200 |
| ATA: B757-300 |
| American Airlines: MD-11S |
+--------------------------------+
4 rows in set

If you want the REGEXP operator to compare the strings in a case-sensitive manner, you can use the BINARY operator to convert the string to a binary string. Because MySQL compares binary bytes byte by byte instead of character by character. This allows string comparisons to be case-sensitive. For example, the following statement matches only product names that begin with an uppercase "C":

SELECT 
 productname
FROM
 products
WHERE
 productname REGEXP BINARY '^C';

Execute the above query statement and get the following results:

+--------------------------+
| productname |
+--------------------------+
| Collectable Wooden Train |
| Corsair F4U (Bird Cage) |
+--------------------------+
2 rows in set

To find productions ending with f, we can use '$f' to match the end of the string:

SELECT 
 productname
FROM
 products
WHERE
 productname REGEXP 'f$';

Execute the above query statement and get the following results:

+------------------+
| productname |
+------------------+
| Boeing X-32A JSF |
+------------------+
1 row in set

To find products whose names contain "ford", you would use the following query:

SELECT 
 productname
FROM
 products
WHERE
 productname REGEXP 'ford';

Execute the above query statement and get the following results:

+----------------------------------+
| productname |
+----------------------------------+
| 1968 Ford Mustang |
| 1969 Ford Falcon |
| 1940 Ford Pickup Truck |
| 1911 Ford Town Car |
| 1932 Model A Ford J-Coupe |
| 1926 Ford Fire Engine |
| 1913 Ford Model T Speedster |
| 1934 Ford V8 Coupe |
| 1903 Ford Model A |
| 1976 Ford Gran Torino |
| 1940s Ford truck |
| 1957 Ford Thunderbird |
| 1912 Ford Model T Delivery Wagon |
| 1940 Ford Delivery Sedan |
| 1928 Ford Phaeton Deluxe |
+----------------------------------+
15 rows in set

To find products whose names contain only 10 characters, you can use '^' and '$' to match the beginning and end of the product name and repeat any character {10} times, as in the following query:

SELECT 
 productname
FROM
 products
WHERE
 productname REGEXP '^.{10}$';

Execute the above query statement and get the following results:

+-------------+
| productname |
+-------------+
|HMS Bounty |
| Pont Yacht |
+-------------+
2 rows in set

Actually, this query method is fixed, and what is variable is the regular expression you write, so how to use it specifically depends on the ability of each of you.

Okay, that’s all for today.

PS: Here are two very convenient regular expression tools for your reference:

JavaScript regular expression online testing tool:
http://tools.jb51.net/regex/javascript

Regular expression online generation tool:
http://tools.jb51.net/regex/create_reg

Readers who are interested in more MySQL-related content can check out the following topics: "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.