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.