Oracle Regular Expression Examples :

In my previous article, I have given idea about oracle 12c features like pivot in SQL. In this article, I will give the Oracle Regular Expression Examples. There are lot of new features of oracle one of them is Regular Expressions. User can simply use different regular expressions for so many functionalities. When user needs to find specified pattern from string then Regular expression is used. Regular Expression is nothing but the simple and complex pattern of searching and manipulating string. There are so many scenarios where user needs to use Regular Expression in Oracle.

Regular Expressions are very powerful text processing components in oracle

Usages of Regular Expression in Oracle:

There are so many usages of regular expression. There are following usages of regular expression:

1. Validation Purpose:

There are so many scenarios where user needs to check a certain pattern.

Example:

In email validation, check user needs to add only mails, which has ‘@’ symbol.

2. Avoid sensitive string:

By centralizing the pattern, matching logic user can be able to avoid the sensitive string.

3. Avoid duplicate validation logic:

By using the server side regular expression, you can avoid duplicating validation logic.

4. Search pattern in string:

Regular expressions are used to search the specific pattern from the string.

Example:

If user wants to calculate the count of the commas then regular expression function is useful.

Oracle Regular Expression Examples with different functions  :

REGEXP_LIKE function:

The REGEXP_LIKE function is very important regular expression function that is used in validation of the data. It will reduce the developer’s effort of adding the PLSQL.The REGEXP_LIKE function is mainly used to match the complex pattern, which is just like ‘like’ clause in oracle but it is matching the regular expressions instead of simple pattern of the string.

Syntax:

REGEXP_LIKE (Source String, Pattern, match_parameter);

The source string is any string from which user needs to grab the specific pattern.

Pattern: The pattern is any pattern which user needs to match.

Match parameter:

Match_parameter is nothing but a text literal which is used to change the matching behavior of string.

Following are some matching patterns:

i : i specifies the case insensitive match of the string.

c : If user wants the case sensitive match then needs to use the c matching pattern.

m : ‘m’ treats the source string as multiple lines. Oracle interprets ^ and $ as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, Oracle treats the source string as a single line.

Example 1 :

If user wants to find the employees whose name is Whose first name begins with A and ends with R but in between the string is mi or mee from Employee table.

To achieve this following query is used :

Select * from Employee where REGEXP_LIKE(name,’^A(mi|mee|)t$);

The above query fetches the records of employees whose first name is Amit or Ameet.

Example 2 :

If user wants to find the employees who has double vowel in its first name.

SELECT first_name

FROM employees

WHERE REGEXP_LIKE (first_name, ‘([aeiou])\1’, ‘i’);

These are some important examples of REGEXP_LIKE function.

REGEXP_COUNT :

The REGEXP_COUNT function is used to count the specified string pattern. These kind of conditions are useful for handling the validations. It fetches the count of given pattern appears in the screen. The matching pattern is same as REGEXP_LIKE function.

Syntax:

REGEXP_COUNT (Source String, Pattern to match,Position,match_parameter);

Example 1:

If user wants to calculate how many time a is used in string after 2 positions.

SELECT REGEXP_COUNT(‘Amit Shiravadekar’, ‘a’, 2, ‘c’) FROM dual;

The above query will return the count as 3 as it calculates the count of letter ‘a’ which is case-sensitive.

Example 2:

If user wants to find out the ‘@’ character from the string. The following query is useful in email validations.

SELECT REGEXP_COUNT(‘ASaaaa@gmail.com’, ‘@’) FROM dual;

If the count of ‘@’ is more than one then the email validation fails in that case.

3.REGEXP_INSTR :

The REGEXP_INSTR function that returns an integer that indicates the starting position of the given pattern in the given string. Alternatively, the integer can indicate the position immediately following the end of the pattern. This function is very useful for email validations.

Syntax :

REGEXP_INSTR(String,pattern);

Example :

SELECT

REGEXP_INSTR(‘Amit@gm@ail.com’, ‘\w+@\w+(\.\w+)+’)

from dual;

This query will return value 6 so this mail id is not valid mail id.

SELECT

REGEXP_INSTR(‘Amit@gmail.com’, ‘\w+@\w+(\.\w+)+’)

from dual;

 This query will return value as 1 .

REGEXP_REPLACE :

The REGEXP_REPLACE function returns the string, which replaces the occurrences of given pattern in the given string with replacement string.

Syntax :

REGEXP_REPLACE(Source_String,Pattern,Replace_String);

Example :

This function invocation puts a space after each character in the column Name :

SELECT

REGEXP_REPLACE(‘Ayan’, ‘(.)’, ‘\1   ‘) from dual;

The REGEXP_REPLACE function used above is used to insert the spaces between the string.

Output :

A y a n

REGEXP_SUBSTR :

Just like a substring REGEXP_SUBSTR function is used to check the given pattern in to given string. It will check the string in to given pattern.Therea are so many options to use this function.

Syntax :

REGEXP_SUBSTR(Source_String,Pattern,Start_position,End_position,Option);

Example :

Select REGEXP_SUBSTR(‘Amit’, ‘A m i t’, 1, 1, ‘x’) from dual;

The above function will return output as Amit. These are some important functions of Regular expressions in Oracle. These regular expression functions are used mainly for the validation purpose in sql which will avoid the PL SQL code.

Hope everyone likes this article on Oracle Regular Expression Examples.If you like the article on Oracle Regular Expression Examples then don’t forget to comment in comment section.