Oracle SQL count occurances of character in VARCHAR

Question

Is is possible to count the number of occurrences of a character in a VARCHAR in oracle? Something that would allow me to determine '11001110' has 5 ones?

Answer 1

What version of Oracle?

If you're using Oracle 11.1 or later, you can use REGEXP_COUNT

  1* select regexp_count( '11001110', '1' ) from dual
SQL> /

REGEXP_COUNT('11001110','1')
----------------------------
                           5

In earlier versions, there are various less elegant options such as

SQL> select length( '11001110' ) - length( replace( '11001110', '1' ) ) cnt
  2    from dual;

       CNT
----------
         5