In this tutorial, we are going to discuss the usage of the REGEXP_LIKE() function in Oracle databases. This function allows us to perform a regular expression pattern matching on a given set of values.
Oracle REGEXP_LIKE Function
The REGEXP_LIKE function is similar to the LIKE conditional operator. However, unlike the LIKE operator, this function performs a regex pattern match instead of a single pattern matching.
This makes it more advanced and reliable compared to the LIKE operator. This is because regular expression allows us to specify a more precise and customizable pattern. This can quickly help to narrow down the search results within a given set.
The function syntax is as described below:
The function accepts the arguments as shown below:
- source_string – the source_string parameter defines the string that needs to be searched. This can be a literal string or a column of supported data type. Supported data types include CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, etc.
- search_pattern –this parameter is used to specify the regular expression pattern to be matched to the input string.
The following are the accepted regular expression syntax and combinations:
| Operator | Description |
| ^ | Matches the beginning of a string as defined in the match_parameter. |
| $ | Denotes the end of a string as defined in the match_parameter. |
| * | Match zero or more occurrences |
| + | Match one or more occurrences |
| ? | Match zero or one occurrence |
| ^b | Matches the beginning-of-line character |
| $b | Matches the end-of-line character. |
| . c | Match any character in the supported character set except NULL |
| []d | specifies a list of items and wish to match at least one item in the list |
| () | Allows the pattern to be treated as a single subexpression. |
| {m} | Matches exactly m times. |
| {m,} | Matches at least m times. |
| {m,n} | Matches at least m times but no more than n times. |
| \ne | Backreference expression (where n is an integer between 1 and 9) matches the nth subexpression. |
| [..]f | Specifies the collation element |
| [::]g | Specifies the character classes |
| [==]h | Specifies equivalent classes. |
To discover more about the Multilingual Regular Expression Syntax and Usage in Oracle databases, consider the link provided below:
https://docs.oracle.com/cd/B12037_01/server.101/b10759/ap_posix001.htm#i690819
- match_parameter – this describes a string used to alter the default matching behavior of the function. The match_parameter is similar to that of the REGEXP_SUBSTR() function.
The following shows the accepted values for the match_parameter.
- ‘c’ – performs a case-sensitive search.
- ‘i’ – tells the function to perform a case-insensitive search.
- ‘n’ – allows the period character to match the new line character. By default, it is treated as a wildcard character.
- ‘m’ – allows the expression to have multiple lines where the caret (^) character denotes the start and the dollar sign ($) indicates the end of the line. By default, the expression is assumed to be a single line.
- ‘x’ –allows the function to ignore whitespace characters.
If the match_parameter is not provided, Oracle will assume:
- The period character does not equal a newline character.
- The source_string is a single-line input.
- The default case sensitivity is defined in the NLS_SORT parameter.
Once the function performs the desired operation, it should return the row matching the specified pattern.
Example Usage
The following section provides examples demonstrating the usage of the REGEXP_LIKE function.
Suppose we have a table containing employee information as shown:
Example 1 – Basic Usage
The following example shows how to use the function to search for a matching name in the employees’ table.
first_name, last_name
FROM EMPLOYEES
WHERE regexp_like(first_name, 'P');
In this case, the query above should return all the matching names where the first_name includes ‘P.’
An example output is as shown:
Example 2 – Match the Beginning of the Line
In the example below, we demonstrate how to use the match_parameter to search the beginning of the line denoted by the caret character.
first_name, last_name
FROM employees
WHERE regexp_like(first_name, '^H', 'i');
The resulting table:
Example 3 – Match the End of the Line
Similarly, we can match the pattern at the end of the line using the ‘i’ as the value of the match_parameter and the dollar sign to denote the end of the line.
first_name, last_name
FROM EMPLOYEES
WHERE regexp_like(LAST_NAME, 'r$', 'i');
In this case, the query above should return the matching values as shown:
Notice that all the values of the last_name column end with an ‘r.’
Example 4 – Perform a Case Sensitive Search
In the example below, we set the value of the match_parameter to ‘C’ to enable case-sensitive matching.
first_name, last_name
FROM EMPLOYEES
WHERE regexp_like(FIRST_NAME, 'julia', 'c');
In the above example, since we are using case sensitivity with a lowercase search pattern, the query will not match any values as all the values in the table are camel-cased.
Example 5 – Perform a Case Insensitive Search
To circumnavigate the above scenario, we can perform a case-insensitive matching by setting the value of the match_parameter to ‘i’.
Example:
first_name, last_name
FROM EMPLOYEES
WHERE regexp_like(FIRST_NAME, 'julia', 'i');
Output:
In this case, we can see the query matches two values.
Example 6 – Match Preceding Character Several Times.
We also enable the query to match a specific preceding character a defined number of times. An example is as shown:
FROM EMPLOYEES
WHERE regexp_like(LAST_NAME, 'e{2}', 'i');
In the example query above, we search for all the matching rows where the value contains two ‘e’ characters in the last_name column.
The resulting values are as shown:
As you can see, all the values in the last_name column contains two ‘e’ characters.
Conclusion
In this post, we discussed how to use the REGEXP_LIKE function in Oracle databases to search for a regular expression pattern.
