Sunday, May 9, 2010

Regular Expressions in Oracle 10g

In Oracle Database 10g, you can use both SQL and PL/SQL to implement regular expression support. Regular expressions are a method of describing both simple and complex patterns for searching and manipulating.

To implement regular expression support in either SQL or PL/SQL, you use a new set of functions. These functions are:
Function Name Description
REGEXP_LIKE Similar to the LIKE operator, but performs regular expression matching instead of simple pattern matching
REGEXP_INSTR Searches for a given string for a regular expression pattern and returns the position were the match is found
REGEXP_REPLACE Searches for a regular expression pattern and replaces it with a replacement string
REGEXP_SUBSTR Searches for a regular expression pattern within a given string and returns the matched substring

POSIX Metacharacters in Oracle Database Regular Expressions

Metacharacters are special characters that have a special meaning, such as a wild card character, a repeating character, a nonmatching character, or a range of characters.
You can use several predefined metacharacter symbols in the pattern matching with the functions.
Symbol Description
Matches zero or more occurrences
Alternation operator for specifying alternative matches
Matches the start of line and the end of line
Bracket expression for a matching list matching any one of the expressions represented in the list
If the caret is inside the bracket, it negates the expression.
Matches exactly m times
Matches at least m times but no more than n times
[: :]
Specifies a character class and matches any character in that class
Can have four different meanings: (1) stand for itself; (2) quote the next character; (3) introduce an operator; (4) do nothing
Matches one or more occurrences
Matches zero or one occurrence
Matches any character in the supported character set (except NULL)
Grouping expression (treated as a single subexpression)
Backreference expression
Specifies equivalence classes
Specifies one collation element (such as a multicharacter element)

More on


No comments: