Oracle 11g REGEXP_COUNT

 

 

 

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.