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
|
|
|
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.
|
|
|
Re: Sql Query [message #85365 is a reply to message #85309] |
Fri, 18 June 2004 04:30 |
Nagadeep
Messages: 12 Registered: October 2002
|
Junior Member |
|
|
select length(replace(translate('new delhi','0123456789abcdefghijklmnopqrstuvwxyz',' e '),' ','')) from dual
/
|
|
|
Re: Sql Query [message #85390 is a reply to message #85309] |
Tue, 22 June 2004 02:34 |
SHUB
Messages: 6 Registered: April 2004
|
Junior Member |
|
|
Well there is an easy way .
in sql prompt type
select length('NEW DELHI')- length(replace('NEW DELHI','E',NULL)) from dual;
|
|
|