Pattern-matching conditions

Pattern-matching conditions are used to compare character data.

Pattern-matching conditions include the LIKE condition and the REGEXP_LIKE condition.

LIKE conditions

The LIKE condition is used for pattern matching. The equality operator (=) indicates a complete match between two character values, while the LIKE condition matches a part of one character value with another by searching for a pattern specified by the second character value within the first.

The LIKE condition uses characters defined by the input character set to compute strings.

LIKE condition syntax

char1 [NOT] LIKE char2 [ ESCAPE esc_char ]

Parameters

Considerations

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

All character expressions (char1, char2, and esc_char) can be of any data type, such as CHAR, VARCHAR2, NCHAR, or NVARCHAR2. If their data types differ, OceanBase Database converts them to the data type of char1.

Patterns can contain the following special pattern matching characters:

• The underscore (_) matches exactly one character (not one byte in a multibyte character set).

• The percent sign (%) matches zero or more characters (not bytes in a multibyte character set). The pattern "%" cannot match NULL.

• When no wildcards are present, LIKE can be converted to =. For example, SELECT * FROM t1 WHERE c1 LIKE 'ABC'; is equivalent to "filter("c1"='abc')".

You can use the ESCAPE clause to include actual characters such as % or _ in the pattern. If an escape character precedes a % or _ in the pattern, OceanBase Database interprets the character literally rather than as a special pattern matching character. You can also search for the escape character itself. For example, if @ is the escape character, you can use @@ to search for @.

In the following examples, if x matches the pattern y, the result is TRUE. In y, the % character matches any string of zero or more characters, excluding NULL; the _ character matches any single character. Any character can follow ESCAPE except for the percentage (%) and underscore (_) characters. If an escape character precedes a wildcard, the wildcard is treated as a literal.

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

Examples

The following statement uses the LIKE condition. The ESCAPE '\' clause specifies that the pattern matching characters following the backslash (\) in %A\_B% are treated as regular characters.

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

REGEXP_LIKE condition

REGEXP_LIKE is used for regular expression matching. REGEXP_LIKE evaluates the string based on the input character set.

Syntax of the REGEXP_LIKE condition

REGEXP_LIKE(source_char, pattern [, match_param ])

Parameters

Considerations

The pattern can contain up to 512 bytes. If the data type of pattern is different from that of source_char, OceanBase Database converts pattern to the data type of source_char.

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

• 'i' specifies case-insensitive matching.

• 'c' specifies case-sensitive matching.

• 'n' allows the dot (.) to match a newline character. If you omit this parameter, the dot does not match a newline character.

• 'm' indicates that the source string is treated as a multiline string. OceanBase Database interprets ^ and $ as the beginning and end of any line in the source string, not just the beginning and end of the entire source string. If you omit this parameter, OceanBase Database treats the source string as a single line.

• 'x' indicates that whitespace characters are ignored. By default, whitespace characters match themselves.

If you specify multiple conflicting values for match_parameter, OceanBase Database uses the last specified value. For example, if you specify 'ic', OceanBase Database uses case-sensitive matching. If you specify a character that is not one of the values listed above, an error is returned.

If you omit match_parameter, the following matching rules apply:

• The default case sensitivity is determined by the value of the NLS_SORT system variable.

• The dot (.) does not match a newline character.

• The source string is treated 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 names and surnames of employees whose names are Steven or Stephen (where first_name starts with Ste, ends with en, and has v or ph in the middle).

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 surnames of employees whose surnames contain two adjacent vowels (a, e, i, o, or u), regardless of case.

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