Updated May 12, 2023
Introduction to Oracle REGEXP_REPLACE
The Oracle Regexp_Replace function can be defined as an extension of the REPLACE function introduced in the version 10g as it allows the user to search a string for a regular expression pattern which means it allows us to replace a sequence of characters in a string with a different sequence of characters sent as a parameter in the function using the process of regular expression pattern matching and returns the formatted string as an output.
Syntax
The syntax of the regexp_replace function is as shown below.
REGEXP_REPLACE( source_string, pattern [, replacement_string [, start_position [, nth_occurence [, match_parameter ] ] ] ] )
As we can see in the above syntax that there is a lot of parameters present in the syntax. So, let us go through the parameters one by one.
Parameters
- source_string: It refers to the string to search. The data type of the string can be CHAR, VARCHAR2, NVARCHAR, CLOB.
- Pattern: It refers to the regular expression for which we want to search the source string.
- replacement_string: It is optional parameters and it refers to the string which will replace the matched pattern in the source string. One important point is that since it is optional, the default value is null.
- Start_position: This is also an optional parameter. The start_position refers to the position in the source string from where the search will actually start. The default position if nothing is mentioned is 1. It takes an integer as value
- nth_occurence: This parameter refers to the occurrence or position in the string to be replaced. It takes an integer as value and it is also an optional parameter.
- Match_parameter: This parameter allows the user to modify the matching behavior of the function. It is also an optional parameter.
So, in all there are six parameters in the function, and out of that four are optional.
How REGEXP_REPLACE works in Oracle?
Let us now discuss how the function regexp_replace works in Oracle. The function has basically six parameters out of which two are compulsory and four are optional. When we execute the function the function applies itself to the source string or the string which we want to search. We provide a regex expression or regex pattern in the function in which the function actually searches in the string for the pattern and if it matches then it replaces that with the replacement string which is also a parameter in the function. The optional parameters like start sequence can help us modify the function to search for the pattern form a specific position in the string and similarly, the nth occurrence parameter tells us about a particular appearance of the pattern in the string which if omitted is one.
One important point to note, once the function is executed successfully it returns the original string with the matched pattern replaced by another string.
Examples to Implement Oracle REGEXP_REPLACE
Let us now look at some examples to get a further understanding of how the function works and displays results.
1. Remove special characters from the string
This function can be used to replace special characters present anywhere in the string. So, in the example, we will use this function to remove special characters from a string. Let us look at the query
Code:
SELECT
REGEXP_REPLACE('Hello@ Th♥is∞ is a Mumbai☻ in Quarantine♫','[^a-z_A-Z ]')
FROM
dual;
Explanation: In this query, we have the first parameter as the source string which consists of special characters and we have used [^a-z_A-Z ] as the pattern to replace them. In this, the function will match all the characters present inside the square brackets. ^ represents the beginning of the string. The other characters will be replaced by null as we have not used the replacement string so their default value is null.
Output: Let us run the query in SQL developer and check the result.
As we can see in the output screenshot the new string shows no special characters.
2. Replace the first word in a sentence
In this case, we will replace the first word of the sentence with a different word based on the regex pattern match. In this example we have an original string as “Monday is a great day” and we are going to replace the first word Monday with Friday. Let us look at the query
Code:
SELECT REGEXP_REPLACE ('Monday is a great day', '^(\S*)', 'Friday') NEW_STRING
FROM dual;
Here the regex pattern we have used is ^(\S*).
- ^: It represents the start of the string.
- \S: It matches a non-whitespace character.
- *: It matches zero or more characters.
Output: Let us execute the query in SQL developer and check the result.
As per the output the word ‘Monday’ has been replaced with the word ‘Friday’.
3. Function used to mask Sensitive Information
It is one of the USPs of this function is that it can be used to mask sensitive information. In this example, we will mask the last four digits of the account number of any bank as bank account numbers are very sensitive information of any customer and hence should be protected. Let us look at the query.
Code:
SELECT
regexp_replace( '00000000187710590',
'(^[[:digit:]]{12})(.*)($)', '\1*****\3' )
Account_Number
FROM
dual;
Explanation: In the above query the regex pattern we have used here is (^[[:digit:]]{12})(.*)($). The regex pattern used here matches the digits in the original number from the position 12 as we only need to mask the last five digits. The original 17 digit account number is 00000000187710590 and the replacement string is \1*****\3. The replacement string also has five Asterix as we need to mask the last five digits.
Output: Let us run the query in SQL developer and check the result.
As we can see in the output screenshot the last five digits of the account number have been masked.
Conclusion
In this article, we learned about the definition of the function along with the syntax of the function. Later on in the article, we also discuss the various cases in which we can use the function along with examples to understand better.
Recommended Articles
This is a guide to Oracle REGEXP_REPLACE. Here we discuss an introduction to Oracle REGEXP_REPLACE with syntax, parameters, and respective examples. You can also go through our other related articles to learn more –
