René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Regular expressions in Oracle

With 10g, regular expressions are finally available in SQL. That is, they were already supported through the owa_pattern package.
The new operators and functions are regexp_like, regexp_instr, regexp_substr and regexp_replace
symbolMatches
.Any character except newline
^Start of a line
$End of a line
*0, 1, or more of the preceding element. The preceding element can be grouped with ()
{n}Exactly n repetitions of the preceding element. The preceding element can be grouped with ()
{n,}Matches n or more repetitions of the preceding element. The preceding element can be grouped with ()
{m,n}Matches between m and n repetitions of the preceding element. The preceding element can be grouped with ()
[abc]Character list, matches a, b OR c.
[g-l]g, h, i, j, k, OR l
|groups alternatives

Character classes

The following character classes are suppored:
  • [[:alnum:]]
    Alphanumeric characters
  • [[:alpha:]]
    Alphabetic characters
  • [[:blank:]]
    blank space characters
  • [[:cntrl:]]
    Control characters
  • [[:digit:]]
    0 through 9, or [0-9]
  • [[:graph:]]
    [[:punct:]] + [[:upper:]] + [[:lower:]] + [[:digit:]]
  • [[:lower:]]
    lowercase alphabetic characters
  • [[:print:]]
    Printable characters
  • [[:punct:]]
    punctuation characters
  • [[:space:]]
    Non-printing space characters
  • [[:upper:]]
    Uppercase alphabetic characters
  • [[:xdigit:]]
    hexadecimal characters
Character classes can be negated: [^[:CHAR-CLASS:]].

Equivalence classes

For example, the following not only matches an a, but also ä.
[[=a=]]

Links