SQL pattern matching enables you to use “_” to match any single character and “%” to match an arbitrary number of characters (including zero characters).
LIKE or NOT LIKE comparison
Find names beginning with 'b%':
SELECT * FROM pet WHERE name LIKE 'b%';
Find names ending with '%fy':
SELECT * FROM pet WHERE name LIKE '%fy';
Find names containing a '%w%'
SELECT * FROM pet WHERE name LIKE '%w%';
Find names containing exactly five characters, use five instances of the '_' pattern character:
SELECT * FROM pet WHERE name LIKE '_____';
The other type of pattern matching provided by MySQL uses extended regular expressions. When you test for a match for this type of pattern, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).
The following list describes some characteristics of extended regular expressions:
“.” matches any single character.
A character class “[...]” matches any character within the brackets. For example, “[abc]” matches “a”, “b”, or “c”. To name a range of characters, use a dash. “[a-z]” matches any letter, whereas “[0-9]” matches any digit.
“*” matches zero or more instances of the thing preceding it. For example, “x*” matches any number of “x” characters, “[0-9]*” matches any number of digits, and “.*” matches any number of anything.
A REGEXP pattern match succeeds if the pattern matches anywhere in the value being tested. (This differs from a LIKE pattern match, which succeeds only if the pattern matches the entire value.)
To anchor a pattern so that it must match the beginning or end of the value being tested, use “^” at the beginning or “$” at the end of the pattern.
To find names beginning with “b”, use “^” to match the beginning of the name:
SELECT * FROM pet WHERE name REGEXP '^b';
To find names ending with “fy”, use “$” to match the end of the name:
SELECT * FROM pet WHERE name REGEXP 'fy$';
To find names containing a “w”, use this query:
SELECT * FROM pet WHERE name REGEXP 'w';
To find names containing exactly five characters, use “^” and “$” to match the beginning and end of the name, and five instances of “.” in between:
SELECT * FROM pet WHERE name REGEXP '^.....$';
