Oracle REGEXP_SUBSTR function

Last update on August 06 2024 10:36:56 (UTC/GMT +8 hours)

Description

The REGEXP_SUBSTR function use used to return the substring that matches a regular expression within a string. This function returns NULL when no matches are found. An empty string can be returned by this function if the regular expression matches a zero-length string.

Uses of Oracle REGEXP_SUBSTR Function

Extracting Substrings: Retrieve specific parts of a string that match a regular expression.

Pattern Matching: Identify and extract patterns within a string, such as dates, email addresses, or URLs.

Data Parsing: Break down and analyze complex strings into meaningful components.

String Validation: Ensure that certain parts of a string conform to specified patterns.

Text Mining: Extract relevant information from large text datasets.

Processing Logs: Extract specific information from log files or other unstructured data sources.

Syntax:

REGEXP_SUBSTR(source_char, pattern
              [, position
                 [, occurrence
                    [, match_param
                       [, subexpr
                       ]
                    ]
                 ]
              ]
             )

Parameters:

Name	Description
source_char	source_char is a character expression that serves as the search value. It is commonly a character column and can be of any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.
pattern	pattern is the regular expression. It is usually a text literal and can be of any of the data types CHAR, VARCHAR2, NCHAR, or NVARCHAR2. It can contain up to 512 bytes. If the data type of pattern is different from the data type of source_char, then Oracle Database converts the pattern to the data type of source_char.
position	The position is a positive integer indicating the character of source_char where Oracle should begin the search. The default is 1, meaning that Oracle begins the search at the first character of source_char.
occurrence	occurrence is a positive integer indicating which occurrence of a pattern in source_char Oracle should search for. The default is 1, meaning that Oracle searches for the first occurrence of a pattern. If the occurrence is greater than 1, then the database searches for the second occurrence beginning with the first character following the first occurrence of the pattern, and so forth. If the occurrence is greater than 1, then the database searches for the second occurrence beginning with the first character following the first occurrence of the pattern, and so forth.
match_param	match_parameter is a text literal that lets you change the default matching behavior of the function. This argument affects only the matching process and has no effect on replace_string. You can specify one or more of the following values for match_parameter:
subexpr	For a pattern with subexpressions, subexpr is a nonnegative integer from 0 to 9 indicating which subexpression in the pattern is to be returned by the function.

Applies to

Oracle 12c, Oracle 11g

Examples: Oracle REGEXP_SUBSTR function

The following example examines the string, looking for the first substring bounded by commas. Oracle Database searches for a comma followed by one or more occurrences of non-comma characters followed by a comma and returns the substring, including the leading and trailing commas.

SELECT  
REGEXP_SUBSTR('the web development tutorial, from w3resource, w3resouce.com',',[^,]+,') 
"REGEXPR_SUBSTR"  FROM DUAL;

Output:

REGEXPR_SUBSTR
------------------
, from w3resource,

The following example examines the string, looking for http:// followed by a substring of one or more alphanumeric characters and optionally, a period (.). Oracle searches for a minimum of three and a maximum of four occurrences of this substring between http:// and either a slash (/) or the end of the string.

SELECT
  REGEXP_SUBSTR('https://www.w3resource.com/exercises',
'https://([[:alnum:]]+\.?){3,4}/?') "REGEXP_SUBSTR"
  FROM DUAL;

Output:

REGEXP_SUBSTR
--------------------------
https://www.w3resource.com/

This example uses the subexpr argument to return the first subexpression in pattern:

 SELECT REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 1) 
"REGEXP_SUBSTR" FROM DUAL;

Output:

REGEXP_SUBSTR
-------------------
123

This example use the subexpr argument to return the forth subexpression in pattern:

SELECT REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 4) 
"REGEXP_SUBSTR" FROM DUAL;

Output:

 REGEXP_SUBSTR
-------------------
78

Previous: REGEXP_REPLACE
Next: REPLACE