As the documentation states, REGEXP_COUNT returns the number of times a pattern occurs in a string.
We can do this to simply count how many times the letter S appears in a string
select job, regexp_count(job,'S') from scott.emp; JOB REGEXP_COUNT(JOB,'S') --------- ---------------------- CLERK 0 SALESMAN 2 SALESMAN 2 MANAGER 0 SALESMAN 2 MANAGER 0 MANAGER 0 ANALYST 1 PRESIDENT 1 SALESMAN 2 CLERK 0 CLERK 0 ANALYST 1 CLERK 0 14 rows selectedToday I used this function to detect/count how many carriage returns in a string using
'S', but no doubt you could use the power of regular expressions to do all sorts of things. One such example is validating email address format.
I've also used it in the past to identify dirty data - those with numeric digits when it should be all alphabetical characters (names).