MySQL Phrasebook

MySQL provides many ways to find text that matches a specific pattern (pattern means something such as "All names that start with M" or "All names where the surname is six or more letters long"). One of the most commonly used and useful patternmatching tools is the LIKE operator.

LIKE allows you to match text against simple patterns that can include these two wildcard characters: percent (%) and underscore (_):

• % matches zero or more characters of any type.

• _ matches exactly one character of any type.

For example, if you want to find all author names that start with M and are at least six characters long, you would type

SELECT name FROM author WHERE name LIKE 'M_____%';

For example, if you want to find all author names that end in the letter k, you would type

SELECT name FROM author WHERE name LIKE '%k';

Caution

Queries that use LIKE patterns that begin with a wildcard cannot use indexes. Beware of this and try to avoid running repetitive queries that use this technique.

Tip

To match a literal % or _ within the context of LIKE, prefix the wildcard with a backslash, as shown in the following:

SELECT * FROM some_table WHERE some_column LIKE '__\%';

To find all text that does not match a given LIKE pattern, precede LIKE with NOT, as in the following:

SELECT name FROM author WHERE name NOT LIKE 'M%';

For more information on the various stringmatching functions, refer to

• All MySQL string-comparison functions, including C-like functions such as strstr():

  http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

• MySQL's full-text indexes, which allow rapid development of a search enginelike functionality within a MySQL application:

  http://mysql.com/fulltext

• Examples of using the complex, powerful (and slow) regular expression function:

  http://mysql.com/regex