Database Reference
In-Depth Information
+---------+
| mercury |
+---------+
• Strings that end with a particular substring:
mysql>
SELECT name FROM metal WHERE name REGEXP 'd$';
+------+
| name |
+------+
| gold |
| lead |
+------+
• Strings that contain a particular substring at any position:
mysql>
SELECT name FROM metal WHERE name REGEXP 'in';
+----------+
| name |
+----------+
| platinum |
| tin |
+----------+
• Strings that contain a particular substring at a specific position:
mysql>
SELECT name FROM metal WHERE name REGEXP '^..at';
+----------+
| name |
+----------+
| platinum |
+----------+
In addition, regular expressions have other capabilities and can perform matches that
SQL patterns cannot. For example, regular expressions can contain character classes,
which match any character in the class:
• To write a character class, use square brackets and list the characters you want the
class to match inside the brackets. Thus, the pattern
[abc]
matches
a
,
b
, or
c
.
• Classes can indicate ranges of characters; use a dash between the beginning and
end of the range.
[a-z]
matches any letter,
[0-9]
matches digits, and
[a-z0-9]
matches letters or digits.
• To negate a character class (“match any character but these”), begin the list with a
^
character. For example,
[^0-9]
matches anything but digits.
MySQL's regular-expression capabilities also support POSIX character classes. These
match specific character sets, as described in the following table:
