SELECT LENGTH('ABCEF AYZA') - LENGTH(REPLACE('ABCEF AYZA','A')) 
    FROM DUAL ;

SQL> SELECT LENGTH('AAAAAAAA') - LENGTH(REPLACE('AAAAAAAA','A')) N1,
  2         LENGTH('AAAAAAAA') - NVL(LENGTH(REPLACE('AAAAAAAA','A')),0) N2
  3    FROM DUAL
  4  /

        N1         N2
---------- ----------
                    8

1 row selected.

SQL> 

 SELECT  REGEXP_COUNT( 'bafewrdffaaaa','A', 1,'i' ) 
   FROM  DUAL