MySQL in a Nutshell, 2nd Edition

Regular Expressions

When using the operators REGEXP, RLIKE, and NOT REGEXP, you may need special characters and parameters to be able to search for data based on regular expressions. Table B-5 lists the special characters, and Table B-6 shows special constructs that may be used. In keeping with convention, patterns to match are given within quotes. As an example of a regular expression used with a SELECT statement, suppose that we want to find the name of a particular student in a college’s database, but we can’t quite remember his last name. All we remember is that it’s something like Smith, but it could be Smithfield or maybe Smyth. We could run an SQL statement like the following to get a list of possibilities:

SELECT student_id, 
CONCAT(name_first, SPACE(1), name_last) AS Student
FROM students
WHERE name_last REGEXP 'Smith.*|Smyth';

As an example using a pattern-matching construct, suppose that we suspect there are a few student records in which the name columns contain numeric characters. Suppose also that there are some student records in which the social_security column contains characters other than numbers or dashes. We could search for them by executing an SQL statement like the following:

SELECT student_id, soc_sec, 
CONCAT(name_first, SPACE(1), name_last) AS Student
FROM students
WHERE CONCAT(name_first, name_last) REGEXP '[[:digit:]]+'
OR soc_sec REGEXP '[[:alpha:]]+';

As an example of a construct using a character name, suppose that the column containing Social Security ...