Home » SQL & PL/SQL » SQL & PL/SQL » occurance of string
occurance of string [message #215084] Fri, 19 January 2007 04:23 Go to next message
jaydeep.desai
Messages: 4
Registered: January 2007
Location: Mumbai
Junior Member

how to find the number of occurance of a string in given string (not postiion)?
Re: occurance of string [message #215086 is a reply to message #215084] Fri, 19 January 2007 04:32 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
./fa/456/0/
By
Vamsi
Re: occurance of string [message #215088 is a reply to message #215084] Fri, 19 January 2007 04:37 Go to previous message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
SQL> var a varchar2(200)
SQL> var sub varchar2(3)
SQL> exec :a := 'abcd2335fdfbcd374bcdbcd6438746'; :sub := 'bcd';

PL/SQL procedure successfully completed.

SQL> select (length(:a) - nvl(length(replace(:a,:sub,'')),0))/length(:sub) from dual;

(LENGTH(:A)-NVL(LENGTH(REPLACE(:A,:SUB,'')),0))/LENGTH(:SUB)
------------------------------------------------------------
                                                           4

SQL> exec :sub := 'ggg'

PL/SQL procedure successfully completed.

SQL> select (length(:a) - nvl(length(replace(:a,:sub,'')),0))/length(:sub) from dual;

(LENGTH(:A)-NVL(LENGTH(REPLACE(:A,:SUB,'')),0))/LENGTH(:SUB)
------------------------------------------------------------
                                                           0

SQL> exec :a := 'gggggggggyyyyyggguuuugg';

PL/SQL procedure successfully completed.

SQL> select (length(:a) - nvl(length(replace(:a,:sub,'')),0))/length(:sub) from dual;

(LENGTH(:A)-NVL(LENGTH(REPLACE(:A,:SUB,'')),0))/LENGTH(:SUB)
------------------------------------------------------------
                                                           4

Rgds.
Previous Topic: how to use clob data type?
Next Topic: Why this procedure is behaving typically
Goto Forum:
  


Current Time: Sat Dec 10 12:36:49 CST 2016

Total time taken to generate the page: 0.20116 seconds