This article will be pretty short because I don’t want to write about what are regular expressions capable of (foremost because I am not fond of them). There are many other books explaining it in detail with examples and master regular expressions takes time and practice.
What I will write about here is a brief description of what are regular expressions, what are the Oracle functions supporting regular expressions, what are their benefits, and their drawbacks.
Regular expressions (sometimes referred to as RegExp or RegEx) are set of patterns used to search for a string in order to find it or replace it. They might be used for validation as well (i.e. check whether a phone number is really a phone number, email validation, etc… );
There are five Oracle RegExp functions you may use:
- REGEXP_LIKE – the most common one … similar to LIKE function except it allows you to use regular expressions
- REGEXP_SUBSTR – similar to SUBSTR function allowing you to use regular expressions
- REGEXP_REPLACE – similar to REPLACE function allowing you to use regular expressions
- REGEXP_INSTR – similar to INSTR function allowing you to use regular expressions
- REGEXP_COUNT – this function counts the number of how many times a given pattern occurs in the string
As I said, I am not going to describe these function in detail because it would take a book to give you all the details. There are certain advantages of using these because you can implement very complex logic in them with just one function. This is especially handy when you need to validate input data. However, beware of over-using these or using them in a performance-critical system – they are very demanding on CPU usage !!! If there is a possibility to replace them with a native Oracle built-in functions (translate, instr, like, …) do so. They are much more efficient than regular expressions.