Function To get the number of dots in string variable

[muzu1232004]

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.j hh.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.

[debasisdas]

Try to use the functions REPLACE,TRANSLA TE and LENGTH ,for the purpose.

If u want u can define your own FUNCTION for that using PL/SQL

[amitpatel66]

DECLARE
v_cnt NUMBER := 0;
a VARCHAR2(20) := '.............. .....';
BEGIN
FOR I IN 1..LENGTH(a) LOOP
IF(SUBSTR(a,i,1 ) = '.') THEN
v_cnt:= v_cnt + 1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT _LINE(v_cnt);
v_cnt:=0;
END;
/