Home » SQL & PL/SQL » SQL & PL/SQL » To find the number of occurences of a string in a clob
To find the number of occurences of a string in a clob [message #260057] Fri, 17 August 2007 05:26 Go to next message
hannah.kamali
Messages: 10
Registered: July 2007
Location: Banglaore
Junior Member
Hi All,

Can somebody help me with a query to find the number of occurrences of a particular string in a Clob.

Thanks in advance

Hannah
Re: To find the number of occurences of a string in a clob [message #260059 is a reply to message #260057] Fri, 17 August 2007 05:38 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
It depends which database version you have. On 9i, you could do something like:

declare
   myclob clob := 'ABCEEEEEABCEEEEEEABCEEEEEEEABCEEEEEEEEEEEEABCEEEEEEEEEEEEEABCEEEEEEEEEEABC';
   string varchar2(3) := 'ABC';
   result number;
begin
   result := (length(myclob) - length(replace(myclob, string, '')))/length(string);
   dbms_output.put_line(result);
END;

Re: To find the number of occurences of a string in a clob [message #260063 is a reply to message #260057] Fri, 17 August 2007 05:56 Go to previous messageGo to next message
hannah.kamali
Messages: 10
Registered: July 2007
Location: Banglaore
Junior Member
Hi,

Thanks for your response.

I'm currently working with Oracle 10g.

If you don't mind, could you please elaborate on the length() and the length(replace()) functions.

With 10g, this anonymous block returns 0.

If possible, please let me know the changes to be incorporated to make this work with 10g too.

Thanks in advance,
Hannah
Re: To find the number of occurences of a string in a clob [message #260064 is a reply to message #260057] Fri, 17 August 2007 06:00 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
There is no reason why this shouldn't work in 10g, although thre are probably better ways of doing it using regular expressions. Did you turn serveroutput on ?
Re: To find the number of occurences of a string in a clob [message #260065 is a reply to message #260064] Fri, 17 August 2007 06:06 Go to previous messageGo to next message
hannah.kamali
Messages: 10
Registered: July 2007
Location: Banglaore
Junior Member
Yes I did.
This is what I see:

SQL> declare
2 myclob clob := 'ABCEEEEEABCEEEEEEABCEEEEEEEABCEEEEEEEEEEEEABCEEEEEEEEEEEEEABCEEEEEEEEEEABC';

3 string varchar2(3) := 'ABC';
4 result number;
5 begin
6 result := (length(myclob) - length(replace(myclob, string, '')))/length(string);
7 dbms_output.put_line(result);
8 END;
9 /
0

PL/SQL procedure successfully completed.

Any chances of improvement?! Sad
Please help.

Thanks!

[Updated on: Fri, 17 August 2007 06:07]

Report message to a moderator

Re: To find the number of occurences of a string in a clob [message #260071 is a reply to message #260065] Fri, 17 August 2007 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> declare
  2     myclob clob := 'ABCEEEEEABCEEEEEEABCEEEEEEEABCEEEEEEEEEEEEABCEEEEEEEEEEEEEABCEEEEEEEEEEABC';
  3     string varchar2(3) := 'ABC';
  4     result number;
  5  begin
  6     result := (length(myclob) - length(replace(myclob, string, '')))/length(string);
  7     dbms_output.put_line(result);
  8  END;
  9  /
7

PL/SQL procedure successfully completed.

Any chance you made something wrong?
Post your full version: 4 decimals.

Regards
Michel
Re: To find the number of occurences of a string in a clob [message #260072 is a reply to message #260057] Fri, 17 August 2007 06:16 Go to previous messageGo to next message
hannah.kamali
Messages: 10
Registered: July 2007
Location: Banglaore
Junior Member
Hi,
I just made a small change and now it works!

I blindly replaced
result := (length(myclob) - length(replace(myclob, string, '')))/length(string);

with

result := (length(myclob) - length(replace(myclob, string)))/length(string);

SQL> declare
2 myclob clob := 'ABCEEEEEABCEEEEEEABCEEEEEEEABCEEEEEEEEEEEEABCEEEEEEEEEEEEEABCEEEEEEEEEEABC';

3 string varchar2(3) := 'ABC';
4 result number;
5 begin
6 result := (length(myclob) - length(replace(myclob, string)))/length(string);
7 dbms_output.put_line(result);
8 END;
9 /
7 <--- corrected by OP, it was 0 in the original message

PL/SQL procedure successfully completed.

I searched the net, but couldn't get a good explanation for the length() and length(replace()) functions you've used. If this doesn't take much of your time, please explain the logic behind the line
result := (length(myclob) - length(replace(myclob, string)))/length(string);

Thanks again!

Hannah

[Updated on: Fri, 17 August 2007 12:31] by Moderator

Report message to a moderator

Re: To find the number of occurences of a string in a clob [message #260076 is a reply to message #260057] Fri, 17 August 2007 06:26 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
It's just basic maths. You remove the 'ABC' strings from the line by using a replace function to replace them with an empty string. The result will be a string of Es in this case. Subtracting the length of this string from the length of the original string gives you the length of all the ABCs in the string. Dividing this by the length of ABC (3) gives you the number of ABCs.
Re: To find the number of occurences of a string in a clob [message #260077 is a reply to message #260071] Fri, 17 August 2007 06:27 Go to previous messageGo to next message
hannah.kamali
Messages: 10
Registered: July 2007
Location: Banglaore
Junior Member
Hi Michel,

Here is my version detail: Oracle 10.2.0.1.0

Thanks!
Re: To find the number of occurences of a string in a clob [message #260078 is a reply to message #260072] Fri, 17 August 2007 06:33 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Just in case you didn't understand the explanation, here's how it looks like (ugly, of course); however, I don't understand what prevented YOU to try it yourself.
SQL> select length('ABCEEEEEABCEEEEEEABCEEEEEEEABCEEEEEEEEEEEEABCEEEEEEEEEEEEEAB
CEEEEEEEEEEABC') len_whole_string from dual;

LEN_WHOLE_STRING
----------------
              74

SQL> select replace('ABCEEEEEABCEEEEEEABCEEEEEEEABCEEEEEEEEEEEEABCEEEEEEEEEEEEEA
BCEEEEEEEEEEABC', 'ABC') replaced_abc from dual;

REPLACED_ABC
-----------------------------------------------------
EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE

SQL> select length('EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE') len_
only_e from dual;

LEN_ONLY_E
----------
        53

SQL> select length('ABC') len_abc from dual;

   LEN_ABC
----------
         3

SQL> select (74 - 53) / 3 from dual;

 (74-53)/3
----------
         7

SQL>
Re: To find the number of occurences of a string in a clob [message #260081 is a reply to message #260077] Fri, 17 August 2007 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you read and followed How to format your posts, maybe it would be easier to see if you did something wrong.

Regards
Michel
Re: To find the number of occurences of a string in a clob [message #260092 is a reply to message #260081] Fri, 17 August 2007 07:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The code that was posted and said not to work:
declare
myclob clob := 'ABCEEEEEABCEEEEEEABCEEEEEEEABCEEEEEEEEEEEEABCEEEEEEEEEEEEEABCEEEEEEEEEEABC';

string varchar2(3) := 'ABC';
result number;
begin
result := (length(myclob) - length(replace(myclob, string, '')))/length(string);
dbms_output.put_line(result);
END;
/
works just fine for me on 10.2.0.1.0.
Re: To find the number of occurences of a string in a clob [message #260183 is a reply to message #260092] Fri, 17 August 2007 12:00 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OP acknowledged that in message #260072 (after correcting a mistake he made).
Re: To find the number of occurences of a string in a clob [message #260188 is a reply to message #260183] Fri, 17 August 2007 12:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the precision otherwise the end of the topic would be incomprehensible.
I changed again the message to reflect the modification.

Regards
Michel
Re: To find the number of occurences of a string in a clob [message #261539 is a reply to message #260057] Thu, 23 August 2007 00:12 Go to previous message
hannah.kamali
Messages: 10
Registered: July 2007
Location: Banglaore
Junior Member
Thank you all! and sorry for the delayed thanks! Razz
Previous Topic: connect by loop in user data
Next Topic: Stored Procedure to merge Two tables
Goto Forum:
  


Current Time: Thu Dec 08 04:12:07 CST 2016

Total time taken to generate the page: 0.19988 seconds