Oracle Regular Expression

Hi,

Are you familiar with the Oracle's Regular Expression (RE) that added from version 10g on wards.
I think this will help you to recollect the same.

Oracle Database 10g offers four RE functions.You can use these on both SQL and PL/SQL statements.

Symbol Matches

. (dot)  ->  Any character except new line.
^          ->  Start from a line
$          ->  End of  a line
*          ->  0,1 and more of the preceding elements. The preceding element can be grouped with ( )
{n}       ->  Exactly 'n' repetition of the preceding element. 
{n,}      ->  Matches 'n' or more repetitions of the preceding element.
{m,n}   ->  Matches between 'm' and 'n' repetitions of the preceding element.
[abc]   ->  Character list matches  a or b or c
[g-l]     ->  from 'g' to 'l'     ie; g,h,i,j,k,l
?         ->  Matches zero or one occurrence of the preceding sub-expression.


i    -  case insensitive
c   -  case sensitive
m  -  allows for more than one line in source string
n   -  allow the period of matches the new line character

REGULAR EXPRESSION

1. REGEXP_INSTR
2. REGEXP_REPLACE
3. REGEXP_SUBSTR
4. REGEXP_LIKE

REGEXP_INSTR(source,regexp,position,occurrence,return_option,modes)

Returns the beginning or the ending position of a regexp match in the source string. The modes are either 0 or 1. 
zero returns the first character of the occurrence and is the default. One returns the position of the character following the occurrence. 

Example 1 

Select regexp_instr('Mississippi','si',1,2,0,"i") from dual

A: 7

Mississippi  is  the source string

1 indicates the position from where to start searching in the source

2 indicates , from which occurrence of the pattern you wish to search for

0 is the default mode

Example 2

Select regexp_instr('This is a test','a') from dual;

A: 9

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

REGEXP_REPLACE(source, regexp, replacement, position, occurrence, modes) 

Returns the source string with one or all regexp matches replaced

Example 1

Select regexp_replace('Mississippi','si','SI',1,0) from dual;

A: MisSIsSIppi

Example 2

Select regexp|_replace('This is test','t.+t','XYZ') from dual

A: This is  XYZ.

Example 3

Select regexp_replace('Anand Ravi Rame','(.*)(.*)(.*)','\3,\2,\1') from dual

A: Ram Ravi Ananad

Here it replacing 1->3 , 2->2 and 3rd one to 1st one

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

REGEXP_SUBSTR(source, regexp,  position, occurrence, modes) 

Returns the source string with part of source matched by the regular expression

Example 1

Select regexp_substr('a....za...za...z','l[[:alpha:]]{4}') from dual

A: lmnop

Here [[:alpha:]]{4} means 4 alphabetic character after 'l' 

Example 2

Select regexp_substr('yababa dababa do','(ab)') from dual

A: ab

Example 3

Select regexp_substr('yababa dababa do','(ab)\1') from dual

A: abab, ab

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

REGEXP_LIKE(source,   regexp,   modes) 

Returns a Boolean value. The function can be used anywhere the Boolean value is acceptable.

In SQL statement the function can be used only in the WHERE and HAVING clause.

Example 1

Select employee_id 

from employees

where regexp_like(employee_id,.*[56]$)

I just mentioned only few symbols and examples which are commonly used. 

Hope you are clear about this. If you have any suggestions , feel free to add..

Have a nice day .....