Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Beginner
Jul 19, 2004

Count the Number of Occurrences of a String Within a String

The select statement below takes the name "george" and finds how many occurrences of "ge" appear within it.

select (length('george') - length (replace('george','ge',''))) / length('ge') count
from dual
/


     COUNT
----------
         2
Using this information, you could write a function like the one below:


    create function countStr(document varchar2,search varchar2) return number is
    counter number;
    begin
       select (length('george') - length (replace('george','ge',''))) / length('ge') count
       into counter
       from dual;
       return counter;
    end;
SQL> /

Function created.

SQL> select countStr('george','ge') from dual;

COUNTSTR('GEORGE','GE')
-----------------------
                      2

1 row selected.
Chris Farmer
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date