LIKE Clause

Use LIKE to match single characters and patterns found anywhere in a string—beginning, ending, or somewhere in between.

LIKE takes this syntax.

[NOT] LIKE {pattern}

NOT—Optional.

Returns True if the left operand matches the pattern on the right. This operator is case-sensitive. A pattern must be at least two characters.

To match any single character in the string, include an underscore (_). To match any pattern, include a percent sign (%). Starting a pattern with a percent sign returns all words that end with the specified characters. The percent sign indicates that any number of characters can precede the specified ones. Ending a pattern with a percent sign returns all the words that begin with the specified characters. Any number of characters can follow the specified ones. To match a pattern anywhere in a string, the pattern must start and end with a percent sign.

For example, let’s say we have a list of countries. A query that filters on the pattern %a%a% returns Bahamas, Jamaica, Slovakia, and Trinidad and Tobago. If we change the pattern to _a_a___, the query returns Bahamas and Jamaica.

To include a literal percent sign or underscore in a pattern, escape them with a backwards slash (\).

Additional wildcard characters and regular expressions aren’t supported.

Here are some more examples.

This example checks whether a customer name contains the characters “ni” anywhere in the string. If the string contains “ni”, then the condition evaluates to True and the query returns the name.

This query returns city names that contain any single character preceding “lb” and zero or more characters following it.

If we precede the characters “lb” with two underscores, the query returns results that have any two characters before “lb.”

This query matches names that begin with “An.” These names include Andrew Levine, Annette Boone, Annette Cline, and Annie Horne.

This query shows all customer names that don’t contain “po.” These names include Aaron Davies Bruce, Aaron Day, Aaron Dillon, and Aaron Riggs.