A pattern-matching condition is used to compare character data.
Pattern matching conditions include the LIKE condition and the REGEXP condition.
LIKE condition
The LIKE condition is used for pattern matching. It matches a portion of one character value with another by searching for a pattern specified by the second character value within the first value.
Syntax
char1 [NOT] LIKE char2 [ ESCAPE esc_char ]
Note
When the LIKE condition contains the ""||"" character, it is treated as a concatenation operator, and this behavior is controlled by PIPES_AS_CONCAT in sql_mode.
Parameters
Parameter |
Description |
|---|---|
| char1 | A character expression, such as a character column, is called a search value. |
| char2 | A character expression, typically a literal, is called a pattern. |
| esc_char | Character expressions, typically literals.ESCAPEtoesc_charmarked as an escape character. |
Usage instructions
A pattern can contain the following special pattern matching characters:
- An underscore (
_) matches any single character in the value. - A percent sign (
%) indicates zero or more characters in the value. The pattern%cannot match NULL.
You can use the ESCAPE clause to include the actual characters % or _ in the pattern.
Examples
obclient> SELECT last_name FROM emp WHERE last_name LIKE '%A\_B%' ESCAPE '\' ORDER BY last_name;
REGEXP Condition
REGEXP is used for regular expression matching.
Syntax
char1 [NOT] REGEXP pattern
Examples
obclient> SELECT first_name, last_name FROM emp WHERE first_name REGEXP '^Ste(v|ph)en$'
ORDER BY first_name, last_name;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Stephen | Stiles |
| Steven | King |
| Steven | Markle |
+------------+-----------+
3 rows in set
SOUNDS LIKE condition
The SOUNDS LIKE condition is used to compare whether two strings sound similar.
Syntax
expr1 SOUNDS LIKE expr2
Example
obclient> SELECT * FROM emp WHERE last_name SOUNDS LIKE 'King' ORDER BY empno;