MySQL

Matching One of Several Characters with REGEXP

. matches any single character. But what if you wanted to match only specific characters? You can do this by specifying a set of characters enclosed within [ and ], as seen here:

SELECT prod_name
FROM products
WHERE prod_name REGEXP 'product [123]'
ORDER BY prod_name;

The above statement displayed the following output:

+-----------+
| prod_name |
+-----------+
| product 1 |
| product 2 |
| product 3 |
+-----------+

Here the regular expression product [123] was used. [123] defines a set of characters, and here it means match 1 or 2 or 3, so product 1, product 2 and product 3 matched and were returned.

As you have just seen, [] is another form of OR statement. In fact, the regular expression product [123] is shorthand for product [1|2|3], which also would have worked. But the [] characters are needed to define what the OR statement is looking for. To better understand this, look at the next example:

SELECT prod_name
FROM products
WHERE prod_name REGEXP 'product 1|2|3'
ORDER BY prod_name;

The above code displayed the following output:

+---------------+
| prod_name     |
+---------------+
| 1 product     |
| 2 product     |
| 3 product     |
| product 1000  |
| product 2000  |
+---------------+

Well, that did not work. The two required rows were retrieved, but so were three others. This happened because MySQL assumed that you meant '1' or '2' or 'product 3'. The | character applies to the entire string unless it is enclosed with a set.

Sets of characters can also be negated. That is, they'll match anything but the specified characters. To negate a character set, place a ^ at the start of the set. So, whereas [123] matches characters 1, 2, or 3, [^123] matches anything but those characters.