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 ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.