Count Occurrence of a Character in a String

No votes yet

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:

 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!


Have a question? Please ask it on the forum instead.

Post new comment

The question below is to prevent automated spam submissions.
16 + 4 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.