Oracle Regular Expression

Regular expressions allow you to perform powerful context searches in variable-length strings. They provide a a powerful set of pattern matching capabilities by combining the following

Character Classes	are groups of possible characters at a point in the search
Collation Classes	are sets of characters and are treated like a range
Metacharacters	are operators that specify search algorithms
Metasequences	are operators created by two metacharacters or literals
Literals	are characters, character sets and words

Character Classes

Character classes are groups or ranges of possible characters, they are traditionally delimited by square brackets [], You use a dash "-" to specifiy a range i.e [1-9]. You can use the posix character class inside the brackets [:alpha:] see below for more examples

[:alnum:]	All alphanumeric characters
[:alpha:]	All alphabetic characters
[:cntrl:]	All non-printable control characters
[:digit:]	All numeric digits
[:graph:]	all [:digit:], [:lower:], [:punct:] and [:upper:] portable character class
[:lower:]	All lower alphabetic characters
[:print:]	All printable characters
[:punct:]	All punctuation characters
[:space:]	All nonprinting space characters
[:upper:]	All upper alphabetic characters
[:xdigit:]	All hexidecimal characters

Now for an example
Character class example:

DECLARE

  counter            NUMBER := 1;

  source_string   VARCHAR2(12)    := 'A1';

  pattern1           VARCHAR2(12)   := '[[:alpha:]]';  -- use the posix character class

  pattern2           VARCHAR2(12)   := '[[:alnum:]]';

BEGIN

  -- Compare using standard character class ranges.

  FOR i IN 1..LENGTH(source_string)
  LOOP

    IF REGEXP_INSTR(SUBSTR(source_string,counter,i),pattern1) = i
    THEN

        dbms_output.put(REGEXP_SUBSTR(SUBSTR(source_string,counter,i),pattern1));

    ELSE

        dbms_output.put_line(REGEXP_SUBSTR( SUBSTR(source_string,counter,i),pattern2));                                                           

    END IF;

    counter := counter + 1;

  END LOOP;

END;

/

This class is new and is designed to allow you to collate languages that require a collating element. You define a collation class by using [..]
Collation Classes

Collation class example

[a-[.ch.]] Note: allows you to find element that is between an a or a ch

Metacharacters

A metacharacter provides some mechanics for performing pattern matching, lots of programming and scripting languages use the same syntax.

Metacharacter	Name	Type	Description
()	parentheses	Delimiter	act as a constraint on the scope of comparsion
{m}	exact	Interval	matches exactly m occurences of the preceding subexpression or character
{m, }	at least	Interval	matches at least m occurences of the preceding subexpression or character
{m,n}	between	Interval	matches exactly m occurences but no more than n of the preceding subexpression or character
|	OR	Logical	acts as a logical OR operator
.	dot	Matching	matches any one character
^	caret	Matching	matches the beginning of a line
$	dollar	Matching	matches the end of a line
[^]	caret	Negation	negates when used inside square brackets (like the NOT operator)
-	dash	Range	specifies a range when inside square brackets
?	question mark	Repetition	makes the proceding character optional
*	asterisk	Repetition	matches any instance of zero to many characters
+	plus	Repetition	matches at least once or many times the preceding character.

The best way to learn these is to google regular expression on the web, as i said before many programming languages and scripting languages use metacharacters.

Metasequences

Those of you who have used Perl before will under the metasequences, they are a character combined with a backslash to represent a pattern or characters.

Metasequence	Name	Type	Description
\n	backreference	Posix	matches the nth preceding subexpression
\d	digit	Perl	matches any digit
\D	nondigit	Perl	matches any non-digit
\w	word character	Perl	matches any word characters
\W	nonword character	Perl	matches any non-word characters
\s	whitespace character	Perl	matches any whitespace character
\S	nonwhitespace character	Perl	matches any non-whitespace character
\A	beginning of a string	Perl	matches a beginning of a string
\Z	end of a sting	Perl	matches the end of a string
\z	end of a string	Perl	matches the end of a string

Literals

Literals values are simply string literals, they may consist of one or many characters.

Oracle 11g Regular Expression

Oracle 11g has implemented a number of regular expression functions, so far there are 5 in total

Regular Expression Functions
REGEXP_COUNT	lets you count the number of times a specific pattern is found in a string
REGEXP_INSTR	lets you find a position index value in a string
REGEXP_LIKE	lets you find a regular expression match inside a string, it's like the LIKE operator
REGEXP_REPLACE	lets you find and replace a substring inside of a string
REGEXP_SUBSTR	lets you find a substring inside a string
Match Type Flags
i		sets the search to case-insensitive matching, overriding the nls_sort parameter
c		sets the search to case-sensitive matching, overriding the nls_sort parameter
n		Enables the dot (.) to truly match any character, including the newline
m		Enables a search to recognise multiple lines inside a string
x		sets the search to ignore any whitespace characters
Examples
REGEXP_COUNT			DECLARE    mystring varchar2(24) := 'abcdefABCDEFabcdefABCDEF';    mycount number; BEGIN    -- match a patterm - should result in 2    select regexp_count(mystring, 'abcdef')        into mycount      from dual;    dbms_output.put_line('Total Matches: ' || mycount);    -- match a single pattern starting from position 7 - should result in 1    select regexp_count(mystring, 'abcdef',7)         into mycount      from dual;    dbms_output.put_line('Total Matches: ' || mycount);    -- match a pattern but ignore case - should result in 4    select regexp_count(mystring, 'abcdef',1,'i')       into mycount      from dual;    dbms_output.put_line('Total Matches: ' || mycount); END; /
REGEXP_INSTR			create table employees (emp_id        number                                     ,emp_name  varchar2(30)                                     ); insert into employees values (1, 'Paul Valle'); select regexp_instr(emp_name,'Valle',1,1,0,'i') as Begin     regexp_instr(emp_name,'Valle',1,1,1,'i') - 1 as End from employees
REGEXP_LIKE			create table employees ( emp_id       number                                     ,emp_name  varchar2(30)                                     ); insert into employees values (1, 'Paul Valle'); select emp_id  from employees where regexp_like(emp_name, 'Valle');
REGEXP_REPLACE			create table employees (emp_id       number                                     ,emp_name  varchar2(30)                                     ); insert into employees values (1, 'Paul Valle'); select regexp_replace(emp_name, 'Paul', 'Lorraine',1)   from employees;
REGEXP_SUBSTR			create table employees ( emp_id       number                                      ,emp_name  varchar2(30)                                     ); insert into employees values (1, 'Paul Valle'); select regexp_substr(emp_name, 'V[:alpha:]+e',1)   from employees;   -- using a character class