REGEXP_SUBSTR(source_string, pattern
[, position [, occurrence
[, match_parameter]]])
 
 
/*
'source_string' is a character expression that serves as the 
search value.
 
'pattern' is the regular expression. It is usually a text literal.
 
'position' is a positive integer indicating the character of 
'source_string' where Oracle should begin the search. The 
default position is 1.
 
'occurrence' is a positive integer indicating which occurrence 
of pattern in source_string Oracle should search for. 
The default is 1.
 
'match_parameter' is a text literal that lets you change 
the default matching behavior of the function.
 
'match_parameter' can be a combination of
 
i: to match case insensitively
c: to match case sensitively
n: to make the dot (.) match new lines as well
m: to make ^ and $ match beginning and end of 
   a line in a multiline string 
*/
 
-- Searches for a comma followed by one or more occurrences of 
-- non-comma characters followed by a comma       
SELECT 
REGEXP_SUBSTR('I had a dog, he had white fur, his name was Bob.',[^,]+,') 
RESULT FROM dual;
 
 
-- search for 'http://' followed by a string of one or more 
-- alphanumeric characters
 
SELECT REGEXP_SUBSTR('Find Oracle code AT http://www.psoug.org/',
'http://([[:alnum:]]+){3,4}/?') RESULT
FROM dual;
 
 
 
-- given the string 'green|red|blue|yellow|black' 
-- in a table column, explode the string along the 
-- pipes (vertical bar) and extract the colors: 
 
SELECT REGEXP_SUBSTR(regex_column,'[^|]+', 1, 3)
FROM regexp;
 
 
-- match 'call', 'caller', 'called', or 'calling' in the string
SELECT 
REGEXP_SUBSTR('You have called me too often, she said.','call((ing)|(er)|(ed))')
FROM dual;