REGEXP_COUNT(<source_string>, <pattern>[[, <start_position>], [<match_parameter>]])
^
--------------------------------------------------------------------|
-- match parameter:
'c' = case sensitive
'i' = case insensitive search
'm' = treats the source string as multiple lines
'n' = allows the period (.) wild character to match newline
'x' = ignore whitespace characters
Total how many 123 in this string '123123123123' !!!!!!!! (in total string )
SQL> SELECT REGEXP_COUNT('123123123123', '123') COUNT FROM DUAL;
COUNT
----------
4
Total how many 123 in this string '123123123123' from 4th place
|<-----------------|
SELECT REGEXP_COUNT('123123123123', '123', 4) COUNT FROM DUAL;
^
|--- this is 4th place , from this place how many 123
COUNT
----------
3
Total how many 123 in this string '123123123123' from 5th place
|<---------------|
SQL> SELECT REGEXP_COUNT('123123123123', '123',5) COUNT FROM DUAL;
^
|--- this is 5th place , from this place how many 123
COUNT
----------
2
source string at the third character for case 'i' specifies case-insensitive matching.
SELECT REGEXP_COUNT('123123123123', '123', 3, 'i') COUNT FROM DUAL;
COUNT
----------
3
SELECT REGEXP_COUNT('123123123123123', '(12)3', 1, 'i') REGEXP_COUNT FROM DUAL; ---------'(12)3' ='123'
REGEXP_COUNT
------------
5
CREATE TABLE test (testcol VARCHAR2(50));
INSERT INTO test VALUES ('abcde');
INSERT INTO test VALUES ('12345');
INSERT INTO test VALUES ('1a4A5');
INSERT INTO test VALUES ('12a45');
INSERT INTO test VALUES ('12aBC');
INSERT INTO test VALUES ('12abc');
INSERT INTO test VALUES ('12ab5');
INSERT INTO test VALUES ('12aa5');
INSERT INTO test VALUES ('12AB5');
INSERT INTO test VALUES ('ABCDE');
INSERT INTO test VALUES ('123-5');
INSERT INTO test VALUES ('12.45');
INSERT INTO test VALUES ('1a4b5');
INSERT INTO test VALUES ('1 3 5');
INSERT INTO test VALUES ('1 45');
INSERT INTO test VALUES ('1 5');
INSERT INTO test VALUES ('a b c d');
INSERT INTO test VALUES ('a b c d e');
INSERT INTO test VALUES ('a e');
INSERT INTO test VALUES ('Steven');
INSERT INTO test VALUES ('Stephen');
INSERT INTO test VALUES ('111.222.3333');
INSERT INTO test VALUES ('222.333.4444');
INSERT INTO test VALUES ('333.444.5555');
INSERT INTO test VALUES ('abcdefabcdefabcxyz');
commit ;
set pagesize 100
select * from test ;
TESTCOL
--------------------------------------------------
abcde
12345
1a4A5
12a45
12aBC
12abc
12ab5
12aa5
12AB5
ABCDE
123-5
12.45
1a4b5
1 3 5
1 45
1 5
a b c d
a b c d e
a e
Steven
Stephen
111.222.3333
222.333.4444
333.444.5555
abcdefabcdefabcxyz
25 rows selected.
search for 2a start from 1 and i for ignore case
SELECT REGEXP_COUNT(testcol, '2a', 1, 'i') RESULT FROM test;
RESULT
----------
0
0
0
1
1
1
1
1
1
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
25 rows selected.
search for "e" start from 1 and i for ignore case
SQL> SELECT REGEXP_COUNT(testcol, 'e', 1, 'i') RESULT FROM test;
RESULT
----------
1
0
0
0
0
0
0
0
0
1
0
0
0
0
0
0
0
1
1
2
2
0
0
0
2
25 rows selected.
