| muzu |
Posted: Sep 04, 2007 04:42:57 AM |
Total Post: 37
Joined: Aug, 2007
|
Function To get the number of dots in string variable
--------------------------------------------------------------------------------
Is there any function in SQL to get the number of dots in a string variable.
Suppose
str1 is a varchar2 type variable
str1='adf.hjk.jhh.jhkjh.'
Any function or any method is available in sql to get the number of dots in the string data variable. The result should give the number of dots present i.e it should give 4 as value.
|
|
|
 Mitesh |
| Posted: Sep 04, 2007 05:19:28 AM |  |
|
Total Post: 6
Joined: Aug, 2007
|
Hi,
Try this:
declare
-- Local variables here
v_index integer :=0;
v_cnt integer :=0;
str1 varchar2(200) :='adf.hjk.jhh.jhkjh' ;
begin
-- Test statements here
while true loop
v_index := instr( str1,'.',v_index+1);
exit when v_index =0;
v_cnt :=v_cnt+1;
end loop;
dbms_output.put_line('total number of dots ' || v_cnt);
end;
Thanks
|
|
|
|
|
| gps |
| Posted: Sep 04, 2007 11:03:22 PM | |
|
Total Post: 40
Joined: May, 2006
|
Hi, try this as well ..
set serveroutput on
declare
str1 varchar2(100);
cnt number;
begin
str1:='agsjd.asd.sf.sdf.rt.fgh.';
cnt:=length(str1)-length(translate(str1,'a.','a'));
dbms_output.put_line('No. of dots = '||cnt);
end;
/
Gurwinder
|
|
|
|
|
| bhmadhukar |
| Posted: Feb 21, 2008 03:04:41 AM | |
|
Total Post: 14
Joined: Jan, 2008
|
check 7th page in this forum
>>> How to count number of occurance of a particular character in a string?
SYS>>select occurance('adf.hjk.jhh.jhkjh.','.') from dual;
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
