WITH T AS (SELECT 'R123456' DATA FROM DUAL UNION ALL SELECT 'RR123456' DATA FROM DUAL UNION ALL SELECT 'RRR123456' DATA FROM DUAL UNION ALL SELECT 'RRRR123456R' DATA FROM DUAL UNION ALL SELECT 'RRR1234RR1RR' DATA FROM DUAL) SELECT DATA , LENGTH(DATA) - LENGTH(REGEXP_REPLACE(DATA, '^R{2,}', '')) CNT FROM T