What are Regular Expressions?

Regular Expressions are methods of describing both simple and complex patterns for searching and manipulating. We uses META Characters to define the construct of the search criteria and Oracle’s implementation is an extension of the POSIX (Portable Operating System for UNIX).

The following post is will give the reader an overview of META characters that are used in the Oracle database for Regular Expression pattern matching.

META Characters

Symbol	Description
^	Marks the start of a line
$	Marks the end of a line
[ ]	Matching list
|	Operator for specifying alternative matches (logical OR)
?	Matches zero or one occurrence
.	Matches any character except NULL
{m}	Matches exactly m times
{m,n}	Matches at least m times but no more than n times
[: :]	Specifies a character class and matches any character in the class
\	Escape character
+	Matches one or more occurrences
*	Matches zero or more occurrences
()	Grouping for expression
\n	Back-reference expression

In this first segment, only the META Characters above in Bold will be addressed.

Caret

Dollar Sign

Logical OR – Says if either character set matches, the pattern is valid.

These first three examples are relatively straight forward.  The following set not so much.  After each example, I will provide a breakdown of the process to determine if the pattern is valid.

Dot

 

The dot (.) represents a single character match like the underscore (_) when using simple pattern matching

 

{m} Will match consecutive occurrences of the preceding character the desired number of times.

Valid patterns occur where the letter ‘s’ appears consecutively. Notice that while sister contains the requisite number of the ‘s’ character, they are not consecutive.

 

Star Will match zero or more occurrences of the preceding character

Notice in this instance ‘acc’ is not a valid pattern.  While it matches from the point zero occurrences of the letter ‘b’, it the ‘c’ in the second spot that invalidates this option.  Subsequently, this same argument is why ‘ac’ is valid.

 

There you have it.  Next time will will put these into action as I discuss the 5 Regular Expression Functions.

Enjoy!