-- REGEXP_COUNT syntax:
REGEXP_COUNT(<source_string>, <pattern>
[[, <start_position>], [<match_parameter>]])
 
/*
-- match parameters:
'c' = case sensitive search/match
'i' = case insensitive search/match
'm' = parses the source string as individual lines
's' = parses the source string as a single line
'n' = allows a period (.) wild character to match a newline
'x' = instructs parser to ignore whitespace characters
*/
 
-- count occurrences based on a regular expression: 
SELECT REGEXP_COUNT(regex_col, '2a', 1, 'i') RESULT
FROM regex_test;
 
SELECT REGEXP_COUNT(regex_col, 'e', 1, 'i') RESULT
FROM regex_test;
 
-- REGEXP_INSTR syntax      
REGEXP_INSTR(<source_string>, <pattern>
[[, <start_position>]
[, <occurrence>]
[, <return_option>]
[, <match_parameter>]
[, <sub_expression>]])
 
-- Find words beginning with 's' or 'r' or 'p' followed 
-- by any 4 alphabetic characters (case insensitive) 
SELECT REGEXP_INSTR('The slippery rabbit was pursued by a ravenous wolf', '[o][[:alpha:]]{3}', 1, 1, 0, 'i') RESULT
FROM dual;
 
 
- find the position OF 'calls', 'calling', 'called' OR 'caller'      
SELECT REGEXP_INSTR('You have called me far too many times, she said.', 'call((ing)|(ed)|(s)|(er))') 
RESULTNUM
FROM dual;
 
 
Using sub-expression OPTION      
SELECT regex_col, REGEXP_INSTR(regex_col, 'ab', 1, 1, 0, 'i', 0)
FROM regex_test;
 
 
-- REGEXP_LIKE syntax:      
REGEXP_LIKE(<source_string>, <pattern>, <match_parameter>)
 
 
-- match only alphanumeric characters (a-z, A-Z, and 0-9)
SELECT *
FROM regex_test
WHERE REGEXP_LIKE(regex_col, '[[:alnum:]]');
 
 
-- match only 3 alphanumeric characters (a-z, A-Z, and 0-9)
SELECT *
FROM regex_test
WHERE REGEXP_LIKE(regex_col, '[[:alnum:]]{3}');
 
 
-- match only alphabetic characters (a-z and A-Z)
SELECT *
FROM test
WHERE REGEXP_LIKE(testcol, '[[:alpha:]]');
 
SELECT *
FROM test
WHERE REGEXP_LIKE(testcol, '[[:alpha:]]{3}');