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
[^exp]
If the caret is inside the bracket, it negates the expression.
{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 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)
\n
Backreference expression
[==]
     
Specifies equivalence classes
[..]
     
Specifies one collation element (such as a multicharacter element)

More on
Oracle

:-)

No comments: