A pattern-matching condition compares character data.
LIKE condition is used for pattern matching. The equality operator (=) indicates that one character value exactly matches another character value. The
LIKE condition matches a portion of one character value with another character value by searching the first value for the pattern that is specified by the second character value.
LIKE calculates strings by using the characters that are defined by the input character set.
char1 [NOT] LIKE char2 [ ESCAPE esc_char ]
In addition to
LIKE, the special pattern-matching character
_ indicates that one character in the value is exactly matched.
% indicates that zero or multiple characters in the value are matched. The pattern
% cannot match
The character expression, such as a character column. It is called the search value.
The character expression that is generally a literal. It is called the pattern.
The character expression that is generally a literal.
The following statement uses the
SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\' ORDER BY last_name;
ESCAPE '\'interprets the pattern-matching character _ after
%A\_B%as a general character.
SELECT salary FROM employees WHERE 'SM%' LIKE last_name ORDER BY salary;
REGEXP_LIKE is used for regular expression matching.
REGEXP_LIKE evaluates strings by using the characters that are defined by the input character set.
REGEXP_LIKE(source_char, pattern [, match_param ])
The string expression that is used as the search value. The data type can be
The regular expression. The data type can be
The character expression of the
If the data type of the pattern is different from that of
source_char, ApsaraDB for OceanBase converts the pattern to the data type of
Create the employees table and insert data into the table. Execute the following statements:
CREATE TABLE employees(manager_id INT, first_name varchar(50), last_name varchar(50), hiredate varchar(50),SALARY INT); INSERT INTO employees VALUES(300, 'Steven', 'King', '2019-09-11',23600); INSERT INTO employees VALUES(200, 'Steven', 'Markle', '2019-11-05', 23800); INSERT INTO employees VALUES(100, 'Deven', 'Part', '2018-10-01',24000); INSERT INTO employees VALUES(200, 'Carlos', 'Ross', '2019-06-11',23500); INSERT INTO employees VALUES(200, 'Teven', 'Bell', '2019-05-25', 23000); INSERT INTO employees VALUES(200, 'Stephen', 'Stiles', '2018-06-11',24500); INSERT INTO employees VALUES(100, 'Ame', 'De Haan', '2018-05-01',11000); INSERT INTO employees VALUES(100, 'Jon', 'Errazuriz', '2017-07-21', 1400); COMMIT;
Query the first names and the last names of the employees whose names contain Steven or Stephen.
first_name starts with Ste and ends with en. v or ph is in the middle of the first name. Execute the following statement:
SELECT first_name, last_name FROM employees WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$') ORDER BY first_name, last_name;
The following query result is returned:
+------------+-----------+ | FIRST_NAME | LAST_NAME | +------------+-----------+ | Stephen | Stiles | | Steven | King | | Steven | Markle | +------------+-----------+