All Products
Search
Document Center

Pattern-matching conditions

Last Updated: Jun 18, 2021

A pattern-matching condition compares character data.

LIKE condition

A 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.

Syntax

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 NULL.

Parameters

Parameter

Description

char1

The character expression, such as a character column. It is called the search value.

char2

The character expression that is generally a literal. It is called the pattern.

esc_char

The character expression that is generally a literal. ESCAPE converts the esc_char identity as an escape character. When an escape character is located before a pattern-matching character, the pattern-matching character is interpreted as a general character.

Examples

The following statement uses the LIKE condition:

SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\' ORDER BY last_name;
ESCAPE '\' interprets the pattern-matching character _ after \ in %A\_B% as a general character.
SELECT salary FROM employees WHERE 'SM%' LIKE last_name ORDER BY salary;

REGEXP_LIKE condition

REGEXP_LIKE is used for regular expression matching. REGEXP_LIKE evaluates strings by using the characters that are defined by the input character set.

Syntax

REGEXP_LIKE(source_char, pattern [, match_param ])

Parameters

Parameter

Description

source_char

The string expression that is used as the search value. The data type can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB.

pattern

The regular expression. The data type can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB.

source_char

The character expression of the VARCHAR2 or CHAR data type. This expression allows you to change the default matching behavior of the condition.

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 source_char.

Examples

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    |
+------------+-----------+