Count Occurrence of a Character in a String

Your rating: None Average: 2 (1 vote)

I had the need to count an occurrence of a specific character in a string and after tossing some ideas back and forth between a couple of my colleges, we came up with the below two SQLs. You pass a string and identify the character you are looking to count its occurrence. In both SQLs below, I'm trying to count how many times does ">>" exists in string "This is>>a string>>to look into>>". The answer of course is 3 times.

SQL Option 1:

SELECT COUNT (*)
 FROM (SELECT SUBSTR ('This is>>a string>>to look into>>', ROWNUM, 2 ) s,
              ROWNUM pos
         FROM all_tab_columns
        WHERE ROWNUM <=
                   LENGTH ('This is>>a string>>to look into>>')) a
WHERE s = '>>';

SQL Option 2:

SELECT (LENGTH (line) - LENGTH (TRANSLATE (line, 'x>>', 'x'))) / 2 nbvowels
 FROM (SELECT 'This is>>a string>>to look into>>' line
         FROM DUAL);

I personally prefer option 2. Do you have a better way of achieving the same result? Please share it in the comments section below if you do!

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!