How to count the number of occurrences of a character in an Oracle varchar value?

Watch out for corner cases

There are corner-cases where the basic LENGTH and REPLACE method gets a bit clumsy.

Handling the empty universe 🌌

In case your column might empty, you need to fall back to a default value. COALESCE does that for you! It turns NULL into 0 occurrences:

SELECT COALESCE(LENGTH(column) - LENGTH(REPLACE(column, 'char', '')), 0) AS char_count FROM table;


Now, even the sound of silence (NULL) is counted as 0.

In case of a column of clones

When all characters in the string are the ones to be counted:

SELECT CASE
         WHEN column = RPAD('char', LENGTH(column), 'char') THEN LENGTH(column)
         ELSE LENGTH(column) - LENGTH(REPLACE(column, 'char', ''))
       END AS char_count FROM table;


Now 'aaaaa' results in a count of 5. Like counting sheep, but less sleep-inducing! 🐑

When REPLACE is not enough

In those cases where the character might be part of a regex pattern, use REGEXP_COUNT for superior counting:

SELECT REGEXP_COUNT(column, 'char') AS char_count FROM table;


Behold the power of regex! 🧙‍♂️

Diving deeper with custom functions

When built-in methods falter, it's time for a custom-built solution!

The art of crafting a custom function

You can create a custom EXPRESSION_COUNT function for a flexible (and safe) count:

CREATE FUNCTION EXPRESSION_COUNT(str IN VARCHAR2, expr IN VARCHAR2) RETURN NUMBER IS
  count NUMBER := 0;
  pos NUMBER := 1;
BEGIN
  LOOP
    pos := INSTR(str, expr, pos);
    IF pos = 0 THEN
      EXIT; -- "I must go, my people need me!"
    ELSE
      count := count + 1;
      pos := pos + LENGTH(expr); -- moving along
    END IF;
  END LOOP;
  RETURN count; -- Home sweet home!
END;


SUBSTR, our character chopper

By combining it with SUBSTR, we get our count:

SELECT SUM(CASE WHEN SUBSTR(column, level, 1) = 'char' THEN 1 ELSE 0 END) AS char_count
FROM table,
  TABLE(CAST(MULTISET(SELECT LEVEL FROM dual CONNECT BY LEVEL <= LENGTH(column)) AS sys.OdciNumberList))
GROUP BY column;


Moving beyond single characters

Don't just stop at single characters, count phrases!

SELECT REGEXP_COUNT(column, 'phrase') AS phrase_count FROM table;


Because words speak louder than characters! 📚