Ask Question
Help Others
Home › SQL & PL/SQL

Count Occurrence of a Character in a String

Sun, 03/20/2011 - 9:39pm — Lepa
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!

Bookmark/Search this post with
  • Buzz Up!
  • del.icio.us
  • Digg
  • Facebook
  • Google Buzz
  • Reddit
  • StumbleUpon
  • Technorati
  • Twitter
  • Yahoo
  • Login to post comments
  • 19989 reads
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!