Using LIKE for Pattern Matching

Operators such as = and != are useful for finding values that are equal to or not equal to a specific exact comparison value. When it's necessary to find values based on similarity instead, a pattern match is useful. To perform a pattern match, use value LIKE 'pattern', where value is the value to test and 'pattern' is a pattern string that describes the general form of values to match.

Patterns used with the LIKE pattern-matching operator can contain two special characters (called “metacharacters” or “wildcards”) that stand for something other than themselves:

• Percent (%) Character - The percent character matches any sequence of zero or more characters.For example, the pattern 'a%' matches any string that begins with 'a', '%b' matches any string that ends with 'b', and '%c%' matches any string that contains a 'c'. The pattern '%' matches any string, including empty strings.

• Underscore ( '_' ) Character - The underscore character matches any single character. 'd_g'

matches strings such as 'dig', 'dog', and 'd@g'. Because '_' matches any single character, it

matches itself and the pattern 'd_g' also matches the string 'd_g'.A pattern can use these metacharacters in combination. For example, '_%' matches any string containing at least one character.

For example, to list all countries with names that start with 'United';

mysql> SELECT Name FROM Country

-> WHERE Name LIKE 'United%';

+-------------------------------------------------+

| Name                                                    |

+-------------------------------------------------+

| United Arab Emirates                          |

| United Kingdom                                   |

| United States Minor Outlying Islands |

| United States                                        |

+-------------------------------------------------+

To invert a pattern match, use NOT LIKE rather than LIKE:

mysql> SELECT Name FROM Country

-> WHERE Name NOT LIKE 'United%';

+----------------------------------------------+

| Name                                                |

+----------------------------------------------+

| Aruba                                                | 

| Afghanistan                                      |

| Angola                                              |

...

| South Africa                                     |

| Zambia                                             |

| Zimbabwe                                        |

+----------------------------------------------+

235 rows in set (#.## sec)

Note: The NOT LIKE results in a list which is 4 short of the total Country list (239), since the specified pattern matched items have been left out.