Oracle REGEXP_LIKE function

Preamble

Oracle REGEXP_LIKE function allows regular expressions in the WHERE sentence in SELECT, INSERT, UPDATE or DELETE queries.

Syntax for REGEXP_LIKE in Oracle/PLSQL

REGEXP_LIKE ( expression_id, pattern_id [, match_parameter _id] )

Parameters and function arguments

• expression – a symbolic expression such as a column or field. These can be the following data types: VARCHAR2, CHAR, NVARCHAR2, NCHAR, CLOB or NCLOB.

The pattern is a template. Regular expression for comparison. It can be a combination of the following values:

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

Note:

• The condition REGEXP_LIKE uses an input character set for string evaluation.
• If you specify match_parameter as a 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 matching with more than one alternative

The first Oracle example of the REGEXP_LIKE condition, which we will consider, involves 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 a start match

Next, we 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 the contacts whose last_name starts with ‘A’.

Example of end matching

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

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

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

SQL for Beginners; 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.​