Home » SQL & PL/SQL » SQL & PL/SQL » Query
Query [message #200677] Tue, 31 October 2006 22:14 Go to next message
romi
Messages: 67
Registered: October 2006
Member
Hi All,

there is a string:- 'amrita' or 'baby' or 'bittu'

i want to find the count for each word like:

a is 2 times in amrita.
m is 1 times in amrita.
r .....................
i......................
t......................

My script is:-

Create or replace procedure counting(name in varchar2)
is

l number;
n number;
v_name varchar2(10);
t_name varchar2(10);

begin

l:=length(name);

for i in 1..l loop
v_name:=substr(name,i,1);
for j in i+1..l loop
n:=1;
t_name:=substr(name,j,1);
if v_name=t_name then
n:=n+1;
end if;
end loop;
dbms_output.put_line
(v_name|| ' '||'is'||' '||n||' '||'times'||' '||'in'||' '||name);
end loop;
end;


But it's giving output:-

a is 2 times in amrita
m is 1 times in amrita
r is 1 times in amrita
i is 1 times in amrita
t is 1 times in amrita
a is 1 times in amrita

Can any one tell me that what is wrong in my script.
Re: Query [message #200703 is a reply to message #200677] Wed, 01 November 2006 00:12 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

Compare from begin of the name
so replace for j in i+1..l loop with
for j in 1..l loop

CREATE OR REPLACE PROCEDURE CountIng
(NAME IN VARCHAR2)
IS
l NUMBER;
n NUMBER;
v_Name VARCHAR2(10);
t_Name VARCHAR2(10);
BEGIN
l := Length(NAME);

FOR i IN 1..l LOOP
v_Name := Substr(NAME,i,1);

FOR j IN 1..l LOOP
n := 1;

t_Name := Substr(NAME,j,1);

IF v_Name = t_Name THEN
n := n + 1;
END IF;
END LOOP;

dbms_Output.Put_Line(v_Name || ' is ' || n ||' times in ' ||NAME);


END LOOP;
END;

With the above program if a word like rama is given
you will get
r is 1 times in rama
a is 2 times in rama
m is 1 times in rama
a is 2 times in rama

letter a is repeated twice it may not be necessary for printing twice so we can elemenate this
by following code

CREATE OR REPLACE PROCEDURE CountIng (NAME IN VARCHAR2) IS

l NUMBER;
n NUMBER;
v_Name VARCHAR2(10);
t_Name VARCHAR2(10);
r_name varchar2(10);

BEGIN

l := Length(NAME);

FOR i IN 1..l LOOP
v_Name := Substr(NAME,i,1);


FOR j IN 1..l LOOP
n := 1;

t_Name := Substr(NAME,j,1);

IF v_Name = t_Name THEN
n := n + 1;
END IF;
END LOOP;


if i = 1 then
r_name := v_name;
dbms_Output.Put_Line(v_Name || ' is ' || n ||' times in ' ||NAME);
elsif instr(r_name,v_name)= 0 then
r_name := r_name || v_name;
dbms_Output.Put_Line(v_Name || ' is ' || n ||' times in ' ||NAME);
end if;


END LOOP;

END;
Re: Query [message #200707 is a reply to message #200703] Wed, 01 November 2006 00:43 Go to previous messageGo to next message
romi
Messages: 67
Registered: October 2006
Member
Thanks for it.

This is working for name 'amrita'.

But when i m giving string 'baby',then it is giving output:-
b is 1 times in baby
a is 1 times in baby
y is 2 times in baby

and for 'bittu',it's giving:-

b is 1 times in bittu
i is 1 times in bittu
t is 1 times in bittu
u is 2 times in bittu

why?
Re: Query [message #200709 is a reply to message #200677] Wed, 01 November 2006 00:57 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member


Sorry, i didn't test it because of lack of oracle in my system
The error may be due to N := 1 this must be before the
For j in 1..l loop


Below is the updated one
CREATE OR REPLACE PROCEDURE CountIng (NAME IN VARCHAR2) IS

l NUMBER;
n NUMBER;
v_Name VARCHAR2(10);
t_Name VARCHAR2(10);
r_name varchar2(10);

BEGIN

l := Length(NAME);

FOR i IN 1..l LOOP
v_Name := Substr(NAME,i,1);

n := 1;
FOR j IN 1..l LOOP

t_Name := Substr(NAME,j,1);

IF v_Name = t_Name THEN
n := n + 1;
END IF;

END LOOP;


if i = 1 then
r_name := v_name;
dbms_Output.Put_Line(v_Name || ' is ' || n ||' times in ' ||NAME);
elsif instr(r_name,v_name)= 0 then
r_name := r_name || v_name;
dbms_Output.Put_Line(v_Name || ' is ' || n ||' times in ' ||NAME);
end if;


END LOOP;

END;
Re: Query [message #200711 is a reply to message #200677] Wed, 01 November 2006 01:04 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

Sorry, i didn't test it because of lack of oracle in my system
The error may be due to N := 1 this must be before the
For j in 1..l loop
as N := 0;


Below is the updated one
CREATE OR REPLACE PROCEDURE CountIng (NAME IN VARCHAR2) IS

l NUMBER;
n NUMBER;
v_Name VARCHAR2(10);
t_Name VARCHAR2(10);
r_name varchar2(10);

BEGIN

l := Length(NAME);

FOR i IN 1..l LOOP
v_Name := Substr(NAME,i,1);

n := 0;
FOR j IN 1..l LOOP

t_Name := Substr(NAME,j,1);

IF v_Name = t_Name THEN
n := n + 1;
END IF;

END LOOP;


if i = 1 then
r_name := v_name;
dbms_Output.Put_Line(v_Name || ' is ' || n ||' times in ' ||NAME);
elsif instr(r_name,v_name)= 0 then
r_name := r_name || v_name;
dbms_Output.Put_Line(v_Name || ' is ' || n ||' times in ' ||NAME);
end if;


END LOOP;

END;
Re: Query [message #200729 is a reply to message #200711] Wed, 01 November 2006 01:58 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could just do it as a straight SQL select:

select letter,count(*) 
from  (select substr('bittu',level,1) letter 
       from dual 
      connect by level <= length('bittu')
      )
group by letter;

L   COUNT(*)
- ----------
b          1
i          1
t          2
u          1
Previous Topic: doubt in cursor
Next Topic: problem on creation of table
Goto Forum:
  


Current Time: Sat Dec 10 01:05:05 CST 2016

Total time taken to generate the page: 0.09794 seconds