12C, Database, dbaOnTap, Oracle, REGEXP_REPLACE, Regular Expressions

Pattern Matching – Regular Expressions Part 3: REGEXP_REPLACE


It’s been a couple of months since I’ve been on the blogging scene, so I figured I continue my Regular Expressions 101 series. Part 3 will introduce REGEXP_REPLACE. In my previous post, I went into a bit of detail so on to the good stuff.

Function name Description
REGEXP_LIKE Similar to the LIKE operator but allows for the use of regular expressions in matching
REGEXP_REPLACE Search and replace text using regular expression pattern
REGEXP_INSTR Searches for a string using regular expression pattern and returns the position when match is found
REGEXP_SUBSTR Searches for a string using regular expression pattern and returns the matched substring
REGEXP_COUNT Returns the number of times a pattern appears in the string.


Scenario: Your database has phone numbers stored as ###.###.#### and you need to return the list of phone numbers in the following format: 1 (###)-###-####

Using REGEXP_REPLACE, we can use the ‘.’ as our delimiters and wrap our new formatting around the 3 sections.


As you can see in the statement above, we have dissected the source column in to 3 separate groups by using the metadata characters to represent digits [[:digit:]] and the number of occurrences {m}.  The \ allows us to escape the ‘.’ Now we can now reference them as \1 \2 and \3 in the REGEXP function.

Let’s at a breakdown of this:

Meta Character Description
[[:digit:]]{3} Three digits (group 1)
\. Then a ‘.’ (Since the ‘.’ is a META Character, we have to user the \ to escape it)
[[:digit:]]{3} Three digits (group 2)
\. Then a ‘.’
[[:digit:]]{4} Four digits (group 3)



Replacements Description (sample: 404.777.9311 -> 1(404)777-9311)
1 Start with a 1
(\1) Enclose group 1 in () -> (404)
Add a ‘-‘
\2 Group 2 -> 777
Add ‘-‘
\3 Group 3 -> 9311
RESULT 1(404)777-9311

Since these functions are built into the Oracle database, they are quite useful in other tools as well.  Take a look at my post on using Regular Expressions with ODI.



1 Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.