REGEXP_REPLACE In Oracle With Real Life Example

REGEXP_REPLACE is a utility provided by Oracle to do the replacement. Not only can REGEXP_REPLACE do all the things that function REPLACE can do, but it has some added features too. Most important among them is to do the pattern replacement which most probably you will use it for. In this post we will have a look at the syntax of the function along with the examples of real life usage of the function. But before we start with this function, I’d suggest you to go through these articles to understand how the REGEXP functions work:
REGEXP_LIKE In Oracle With Example
REGEXP_INSTR in Oracle With Example

Syntax

REGEXP_REPLACE( string, pattern [, replacement_string [, start_position [, nth_appearance [, match_parameter ] ] ] ] )

• Here first parameter String will be your input string which you would want to replace patterns in.
• Second Parameter is the pattern i.e. the pattern which needs to be replaced in the source string. If you put ^ before this parameter, Oracle will ignore the characters mentioned after it. in my experience, this omission character is the most important aspect of the function as I am yet to use REGEXP_REPLACE function without it.
• Third Parameter is Replacement String, which will tell Oracle what to replace the pattern with
• Fourth Parameter is Start Position. It is an optional parameter which tells which position should Oracle start looking from for the pattern.
• Fifth parameter is Nth Appearance. Again an optional parameter which tells Oracle about the occurrence of the pattern you want to replace.
• Last parameter is again optional, which tells Oracle what kind of comparison to do e.g. Case Sensitive/ Case Insensitive etc.

REGEXP_REPLACE Example

In my experience, REGEXP_ functions are used most of the time when you are importing or exporting the data from one system to another and few special characters are not allowed in destination system. So you come up with an understanding to remove those special characters with characters which are allowed in destination system. And it is here REGEXP_REPLACE function finds it utility in real life scenario. In our case we had an AR Invoice, having line description containing special characters. Our task was to remove those characters. Here is a simplified version of the code and corresponding output.

By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...

Query1: Simple Replacement in REGEXP_REPLACE

Here is the simplest version of the query. This query gives you the value of column description in it’s original form and the simplest replacement where anything which is not in:
1. Lower Case alphabets
2. Upper Case Alphabets
3. Numeric Characters
4. White Space
5. Double Quotes (“)
That will be replaced by Dollar sign ($). Below is the query and followed by the output

TRX_NUMBER	LINE_NUMBER	DESCRIPTION	SPL_CHAR_REPLACE
10420909	1	2021 LC CONV V8 10AT	2021 LC CONV V8 10AT
10420909	2	3: ULTRA WHITE (083) / TOASTED CARAMEL / SAND TOP (40) – LCCV	3$ ULTRA WHITE $083$ $ TOASTED CARAMEL $ SAND TOP $40$ $ LCCV
10420909	3	LEXUS F/AID KIT W/O VELCRO	LEXUS F$AID KIT W$O VELCRO
10420909	4	ALLOY WHL LCK BLK (21″ CHROME FP PKG)	ALLOY WHL LCK BLK $21″ CHROME FP PKG$
10420909	5	CFM ORCHE (INT: 40)	CFM ORCHE $INT$ 40$$

• Here column 3 has the raw value of the field while 4th column has the replaced value. As you can see, no replacement was done in line number 1 as only alphanumeric and white spaces were present in original data.
• Second and third row had all the special characters replaced.
• Fourth row didn’t replace the double quote as we excluded it from the list.

Query2: Replacement from a particular position in REGEXP_REPLACE

Let’s tweak our query a bit and ask oracle to do the replacement from fifth position and let’s see what happens.

TRX_NUMBER	LINE_NUMBER	DESCRIPTION	SPL_CHAR_REPLACE
10420909	1	2021 LC CONV V8 10AT	2021 LC CONV V8 10AT
10420909	2	3: ULTRA WHITE (083) / TOASTED CARAMEL / SAND TOP (40) – LCCV	3: ULTRA WHITE $083$ $ TOASTED CARAMEL $ SAND TOP $40$ $ LCCV
10420909	3	LEXUS F/AID KIT W/O VELCRO	LEXUS F$AID KIT W$O VELCRO
10420909	4	ALLOY WHL LCK BLK (21″ CHROME FP PKG)	ALLOY WHL LCK BLK $21″ CHROME FP PKG$
10420909	5	CFM ORCHE (INT: 40)	CFM ORCHE $INT$ 40$$

Now let’s have a look at the output of line 2 in first and second query. Difference is that second character “:” is not replaced in the query as we had instructed Oracle to start replacement from 5th position.
Now is the time to use another parameter which is Nth Occurrence. So let’s try it

Query3: Replacement for Nth Occurrence in REGEXP_REPLACE

TRX_NUMBER	LINE_NUMBER	DESCRIPTION	SPL_CHAR_REPLACE
10420909	1	2021 LC CONV V8 10AT	2021 LC CONV V8 10AT
10420909	2	3: ULTRA WHITE (083) / TOASTED CARAMEL / SAND TOP (40) – LCCV	3: ULTRA WHITE (083$ / TOASTED CARAMEL / SAND TOP (40) – LCCV
10420909	3	LEXUS F/AID KIT W/O VELCRO	LEXUS F/AID KIT W$O VELCRO
10420909	4	ALLOY WHL LCK BLK (21″ CHROME FP PKG)	ALLOY WHL LCK BLK (21″ CHROME FP PKG$
10420909	5	CFM ORCHE (INT: 40)	CFM ORCHE (INT$ 40)

Have a look at the output of line 2 and 4. In line 2, replacement happened to only first occurrence of “)” which is second special character beginning from position 5. So only that character was replaced.
While in line 4, last character was replaced which is the second occurrence of special character from 5th position.

Related Posts:

REGEXP_LIKE In Oracle
REGEXP_INSTR in Oracle With Example

Reference:

https://www.techonthenet.com/oracle/functions/regexp_replace.php

Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.

Check out the Amazon Deals of the day based on your interests..