Pattern-matching conditions

Pattern-matching conditions compare character data.

Pattern-matching conditions are LIKE and REGEXP_LIKE.

LIKE condition

The 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 part of a character value with another character value by searching the first value for the pattern specified by the second character value.

The LIKE condition uses characters defined in the input character set to calculate strings.

Syntax

char1 [NOT] LIKE char2 [ ESCAPE esc_char ]

Parameters

Considerations

If esc_char is not specified, no default escape character is available. If the value of char1, char2, or esc_char is NULL, the result is unknown. Otherwise, the escape character (if any) must be a character string of length 1.

All character expressions (char1, char2, and esc_char) can be of any data type, such as CHAR, VARCHAR2, NCHAR, and NVARCHAR2. If the character expressions are of different data types, OceanBase Database will convert all of them into the data type of char1.

Special pattern-matching characters that the pattern can contain are as follows:

• The underscore (_) exactly matches one character in the value rather than one byte of a multi-byte character set.

• The percent sign (%) matches zero or multiple characters in the value rather than bytes in a multi-byte character set. The pattern “%” cannot match NULL.

• If no wildcard character is detected, LIKE can be converted into the equal sign (=). For example, SELECT * FROM t1 WHERE c1 LIKE 'ABC'; is equivalent to “filter(“c1”=‘abc’)”.

You can use the ESCAPE clause to contain actual characters % or _ in a pattern. If an escape character is placed prior to the % or _ character in a pattern, OceanBase Database interprets this character literally, rather than identifying it as a special patter-matching character. You can also search for the escape character itself. For example, if @ is the escape character, you can search for @ by using @@.

In the following example, if x [does not] match pattern y, TRUE is returned. In y, the % character matches any string with zero or multiple characters, except NULL, and the _ character matches any single character. Any character, except the percent sign (%) and underscore (_), can follow the ESCAPE clause. If a wildcard character is preceded with an escape character, the wildcard character is identified as a literal.

x [NOT] LIKE y [ESCAPE 'z']

Examples

In the following statement with the LIKE condition, the ESCAPE '\' clause will interpret the pattern-matching character _ behind \ in %A\_B% as a common character.

SELECT last_name FROM emp WHERE last_name LIKE '%A\_B%' ESCAPE '\' ORDER BY last_name;

REGEXP_LIKE condition

The REGEXP_LIKE condition is used for regular expression matching. It evaluates strings by using the characters defined in the input character set.

Syntax

REGEXP_LIKE(source_char, pattern [, match_param ])

Parameters

Considerations

pattern contains a maximum of 512 bytes. If the data type of pattern is different from that of source_char, OceanBase Database converts the data type of pattern into that of source_char.

You can specify one or more of the following values for match_parameter:

• 'i': indicates case-insensitive matching.

• 'c': indicates case-sensitive matching.

• 'n': indicates allowing the period (.) to match the line feed character. If this option is omitted, the period does not match the line feed character.

• 'm': indicates interpreting the source string as multiple lines. OceanBase Database interprets ^ and $ as the beginning and end of any line in the source string, instead of the beginning and end of the entire source string. If this option is omitted, OceanBase Database identifies the source string as a single line.

• 'x': indicates ignoring whitespace characters. By default, a whitespace character matches itself.

If you specify multiple conflicting values for match_parameter, OceanBase Database will use the last value. For example, if you specify ‘ic’ for match_parameter, OceanBase Database uses case-sensitive matching. If you specify other values, an error is returned.

If match_parameter is omitted, the following matching rules apply:

• By default, the value of the NLS_SORT system variable determines whether case-sensitive matching is adopted.

• The period (.) does not match the line feed character.

• The source string is identified as a single line.

Examples

Create a table named emp and insert data into it.

CREATE TABLE emp(manager_id INT, first_name VARCHAR(50), last_name VARCHAR(50), hiredate VARCHAR(50),salary INT);
INSERT INTO emp VALUES(300, 'Steven', 'King',  '2019-09-11',23600);     
INSERT INTO emp VALUES(200, 'Steven', 'Markle', '2019-11-05', 23800);
INSERT INTO emp VALUES(100, 'Deven',  'Part',  '2018-10-01',24000);     
INSERT INTO emp VALUES(200, 'Carlos', 'Ross',  '2019-06-11',23500);     
INSERT INTO emp VALUES(200, 'Teven',  'Bell', '2019-05-25', 23000);
INSERT INTO emp VALUES(200, 'Stephen', 'Stiles',  '2018-06-11',24500);    
INSERT INTO emp VALUES(100, 'Ame', 'De Haan', '2018-05-01',11000);      
INSERT INTO emp VALUES(100, 'Jon', 'Errazuriz', '2017-07-21', 1400);   
COMMIT;

Query the first name and last name for employees whose first name is Steven or Stephen. (first_name begins with Ste and ends with en, and v or ph lies between.)

obclient> SELECT first_name, last_name FROM emp WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$')
ORDER BY first_name, last_name;
+------------+-----------+
| FIRST_NAME | LAST_NAME |
+------------+-----------+
| Stephen    | Stiles    |
| Steven     | King      |
| Steven     | Markle    |
+------------+-----------+
3 rows in set

Query the last name for employees whose last name contains a double vowel. (last_name contains two adjacent case-insensitive vowels a, e, i, o, or u.)

obclient> SELECT last_name
     FROM emp
     WHERE REGEXP_LIKE (last_name, '([aeiou])\1', 'i')
     ORDER BY last_name;
+-----------+
| LAST_NAME |
+-----------+
| De Haan   |
+-----------+
1 row in set