Wednesday, March 10, 2010

oracle regular expression Pattern Description in 11g

Pattern Description

Anchoring Characters

Character Class

Desc

^

Anchor the expression to the start of a line

$

Anchor the expression to the end of a line


Equivalence Classes

Character Class

Desc

= =

Oracle supports the equivalence classes through the POSIX '[==]' syntax. A base letter and all of its accented versions constitute an equivalence class. For example, the equivalence class '[=a=]' matches ä and â. The equivalence classes are valid only inside the bracketed expression


Match Options

Character Class

Desc

c

Case sensitive matching

i

Case insensitive matching

m

Treat source string as multi-line activating Anchor chars

n

Allow the period (.) to match any newline character


Posix Characters

Character Class

Desc

[:alnum:]

Alphanumeric characters

[:alpha:]

Alphabetic characters

[:blank:]

Blank Space Characters

[:cntrl:]

Control characters (nonprinting)

[:digit:]

Numeric digits

[:graph:]

Any [:punct:], [:upper:], [:lower:], and [:digit:] chars

[:lower:]

Lowercase alphabetic characters

[:print:]

Printable characters

[:punct:]

Punctuation characters

[:space:]

Space characters (nonprinting), such as carriage return, newline, vertical tab, and form feed

[:upper:]

Uppercase alphabetic characters

[:xdigit:]

Hexidecimal characters


Quantifier Characters

Character Class

Desc

*

Match 0 or more times

?

Match 0 or 1 time

+

Match 1 or more times

{m}

Match exactly m times

{m,}

Match at least m times

{m, n}

Match at least m times but no more than n times

\n

Cause the previous expression to be repeated n times


Alternative Matching And Grouping Characters

Character Class

Desc

|

Separates alternates, often used with grouping operator ()

( )

Groups subexpression into a unit for alternations, for quantifiers, or for backreferencing (see "Backreferences" section)

[char]

Indicates a character list; most metacharacters inside a character list are understood as literals, with the exception of character classes, and the ^ and - metacharacters

n match parameter:
'c' = case sensitive
'i' = case insensitive search
'm' = treats the source string as multiple lines
'n' = allows the period (.) wild character to match newline
'x' = ignore whitespace characters

  1. REGEXP_COUNT

REGEXP_COUNT

Syntax

REGEXP_COUNT(source_string, pattern[[, start_position], [match_parameter]])

  1. REGEXP_INSTR

REGEXP_INSTR

Syntax

REGEXP_INSTR(source_string, pattern[[, start_position][, occurrence][, return_option][, match_parameter][, sub_expression]])

  1. REGEXP_LIKE

REGEXP_LIKE

Syntax

REGEXP_LIKE(source_string, pattern, match_parameter)

  1. REGEXP_REPLACE

REGEXP_REPLACE

Syntax

REGEXP_REPLACE(source_string, pattern,
replace_string, position, occurrence, match_parameter)

  1. REGEXP_SUBSTR

REGEXP_SUBSTR

Syntax

REGEXP_SUBSTR(source_string, pattern
[, position [, occurrence
[, match_parameter]]])

No comments: