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. |
REGEXP_REPLACE
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) |
Results
| 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.
OnTap next: REGEXP_INSTR
1 Comment