CREATE OR REPLACE FUNCTION count_char (
p_data VARCHAR2
,p_char VARCHAR2 DEFAULT ‘ ‘
)
RETURN VARCHAR2 IS
v_loc NUMBER := 0;
v_count NUMBER := 0;

v_new_str VARCHAR2(32000);

j binary_integer default 1;

BEGIN

FOR i IN 1 .. LENGTH (p_data) LOOP
IF j > length(p_data) THEN exit; end if;
v_loc := instr(p_data, p_char,j);
IF v_loc > 0 THEN
v_count := v_count + 1;

IF v_count = 1 THEN
v_new_str := substr(p_data,1,(v_loc-1));
ELSE
v_new_str := v_new_str || substr(p_data,(v_loc-j),v_loc-j) ||’‘||p_char||’‘;
–substr(p_data,v_loc+length(p_char),length(p_data));
END IF;

j := length(p_char) + v_loc;

ELSE
v_new_str := v_new_str || substr(p_data,j,1);
j := j + 1;

END IF;

END LOOP;
dbms_output.put_line(v_new_str);
RETURN v_count;
END;
/

How to count number of characters in a string

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

one + 2 =