Regular Expressions 101 - REGEXP_COUNT

Not all regular expressions are scary.

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 selected 

Today I used this function to detect/count how many carriage returns in a string using CHR(13) instead of '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).