Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from MySQL Cookbook

Pattern Matching with SQL Patterns

Problem

You want to perform a pattern match rather than a literal comparison.

Solution

Use the LIKE operator and a SQL pattern, described in this section. Or use a regular expression pattern match, described in Recipe 4.8.

Discussion

Patterns are strings that contain special characters. These are known as metacharacters because they stand for something other than themselves. MySQL provides two kinds of pattern matching. One is based on SQL patterns and the other on regular expressions. SQL patterns are more standard among different database systems, but regular expressions are more powerful. The two kinds of pattern match uses different operators and different sets of metacharacters. This section describes SQL patterns; Recipe 4.8 describes regular expressions.

SQL pattern matching uses the LIKE and NOT LIKE operators rather than = and != to perform matching against a pattern string. Patterns may contain two special metacharacters: _ matches any single character, and % matches any sequence of characters, including the empty string. You can use these characters to create patterns that match a wide variety of values:

  • Strings that begin with a particular substring:

    mysql> SELECT name FROM metal WHERE name LIKE 'co%';
    +--------+
    | name   |
    +--------+
    | copper |
    +--------+
  • Strings that end with a particular substring:

    mysql> SELECT name FROM metal WHERE name LIKE '%er';
    +--------+
    | name   |
    +--------+
    | copper |
    | silver |
    +--------+
  • Strings that contain a particular ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata