Oracle REGEXP_LIKE condition.

Preamble

Oracle condition REGEXP_LIKE allows to execute regular expressions in WHERE proposal in SELECT, INSERT, UPDATE or DELETE queries.

Syntax for REGEXP_LIKE in Oracle/PLSQL

REGEXP_LIKE ( expression, pattern [, match_parameter ] )

Parameters and arguments of the condition

• expression – a symbolic expression such as a column or a field. These can be the following types of data: VARCHAR2, CHAR, NVARCHAR2, NCHAR, CLOB or NCLOB.
• pattern – Template. Regular expression for comparison. This can be a combination of the following values:

• match_parameter – Optional. This allows to change the compliance behavior for the REGEXP_LIKE condition. This can be a combination of the following values:

Note:

The REGEXP_LIKE condition uses an input character set for string evaluation.

If you specify match_parameter as conflict, the REGEXP_LIKE condition will use the last value to break the conflict.

If match_parameter is omitted, the condition REGEXP_LIKE will use case sensitivity as defined by parameter NLS_SORT.

Example of a comparison with more than one alternative

The first Oracle example of the REGEXP_LIKE condition, which we will consider, assumes using the | template.

Let us explain how | template works in Oracle condition REGEXP_LIKE. For example:

SELECT last_name
FROM contacts
WHERE REGEXP_LIKE (last_name, 'Anders(o|e|a)n');

This example REGEXP_LIKE returns all contacts whose last_name is either ‘Anderson’, ‘Andersen’ or ‘Andersan’. The | template indicates that the search should be done with an “o”, “e” or “a”.

Example of matching the beginning

Next, use the condition REGEXP_LIKE to match the beginning of the string. For example:

SELECT last_name
FROM contacts
WHERE REGEXP_LIKE (last_name, '^A(*)');

This example REGEXP_LIKE returns all contacts whose last_name starts with ‘A’.

Example of an end match

Next, use the condition REGEXP_ LIKE to match the end of the line. For example:

SELECT last_name
FROM contacts
WHERE REGEXP_LIKE (last_name, '(*)n$');

This example REGEXP_LIKE will return all contacts whose last_name ends in ‘n’.

Oracle regular expression: regexp like

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.