OraFAQ Forum: Forms » Sql Query

Home » Developer & Programmer » Forms » Sql Query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sql Query [message #85309]

Tue, 15 June 2004 03:11

amit goel
Messages: 20
Registered: March 2004

Junior Member

I want to find out how many times 'e' has come in 'New Delhi' in an SQL Query

Report message to a moderator

Re: Sql Query [message #85312 is a reply to message #85309]

Tue, 15 June 2004 06:10

Himanshu
Messages: 457
Registered: December 2001

Senior Member

Hi,
Make use of following code.

HTH
Regards
Himanshu

create or replace FUNCTION dyn_string1 (ab VARCHAR2)
RETURN Number
AS
t VARCHAR2 (2) := ',';
t2 VARCHAR2 (32767);
t_check NUMBER (10);
t_cnt Number(10):=0;
a VARCHAR2 (32767);
t_check1 NUMBER (10);
BEGIN
a := ab;
-- dbms_output.put_line('The string passed is '||a);
loop
t_check:=0;
If t_cnt=0 then
t_check := INSTR (a, 'e');

If t_check=0 then
exit;
Else
t_check1:=t_check+1;
End If;
t_cnt:=t_cnt+1;
else
t_check := INSTR (substr(a,t_check1), 'e');
If t_check=0 then
exit;
else
t_check1:=(t_check1+t_check+1);
End If;
t_cnt:=t_cnt+1;
end If;
end loop;
RETURN t_cnt;
END;
/
Function created.

SQL> set serveroutput on
SQL> declare
2 a number(10);
3 b varchar2(1000):='New Delhi';
4 begin
5 a:=dyn_string1(b);
6 dbms_output.put_line(a);
7 end;
8 /
2

PL/SQL procedure successfully completed.

Report message to a moderator