LIKE Operator with Wildcards
The LIKE operator is used with wildcard characters to compare a string to a pattern, and return matched values from the database.
You can use these wildcards with LIKE to build your pattern:
- % The percent symbol stands for zero or more characters in a sequence.
- _ The underscore symbol stands for only one character. This single character can also be a number or any punctuation character.
Note: If using the MS Access database, use these wildcards instead of % (percent) and _
(underscore):
- * Asterisk to represent zero or more characters in a sequence.
- ? Question mark to represent a single character.
The LIKE pattern is case insensitive, and is set in single quotes. Employ % and _ with any other character/s (denoted by XY in the table) to search through your database. The wild cards can also be used in combination.
Pattern | Description |
---|---|
LIKE '%X' | Returns values that end with X. These values can have zero or more characters before X. |
LIKE '%XY' | Returns values that end with XY. |
LIKE 'X%' | Returns values that start with X. These values can have zero or more characters after X. |
LIKE '%X%' | Returns values that contain X. These values can have the X character in any position (start, end, second, fourth, etc.) |
NOT LIKE '%X%' | Returns values that do not contain X. |
LIKE 'X%Y' | Returns values that start with X and end with Y. These values can have zero or more characters between X and Y. |
LIKE 'X_' | Returns values that start with X and has one character after X. |
LIKE 'X___' | Returns values that start with X and has three characters (3 underscores) after X. |
LIKE 'X__Y' | Returns values that start with X and end with Y, and has two characters (2 underscores) between X and Y. |
LIKE 'X__%' | Returns values that start with X and has two characters (2 underscores) after X, followed by zero or more characters. |
LIKE '%_X_%' | Returns values that contain X if matches with these rules:
|