What is the function of regexp_substr in Oracle?

2 years ago
Olivia Parker
2 minutes

The REGEXP_SUBSTR function in Oracle database is a regular expression function used to extract substrings that match a specific pattern from a string. Its purpose is to extract substrings from an input string that conform to a specified regular expression pattern.

By using the REGEXP_SUBSTR function, you can extract desired information from a string based on a regular expression pattern. This can be used to search for specific patterns within a string, such as extracting phone numbers, email addresses, URLs, IP addresses, and more.

Here is the usage of the REGEXP_SUBSTR function:

REGEXP_SUBSTR(source_string, pattern, position, occurrence, match_parameter)
  1. Input string, the string from which to extract a substring.
  2. Regular expression pattern that specifies the pattern of the substring to extract.
  3. Position (optional): specifies the starting position for the search for a match, default value is 1.
  4. occurrence (optional): specify the index of the match to extract, default value is 1.
  5. match_parameter (optional): Specifies the matching parameter to modify the default matching behavior.

For example, suppose we have a string ‘Hello, my email is john@example.com’ and we want to extract the email address from it. We can use the following statement:

SELECT REGEXP_SUBSTR('Hello, my email is john@example.com', '[[:alnum:]_.-]+@[[:alnum:]_.-]+') AS email FROM dual;

Executing the above statement will return ‘john@example.com’ as the result.

In summary, the REGEXP_SUBSTR function can extract substrings from a string that match a specific pattern using regular expressions, providing powerful string manipulation capabilities.

bannerAds