-- 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}');