Answer / dinesh

// in SQL:------------------------------------------
Select length('hello')-length(replace('hello','l','')) From
DUAL;

// IN PL/SQL:-------------------------------------------

create or replace Procedure count_char(name varchar2,c varchar2) IS
m number(11):=0;
d number(11);
a number(11);
b number(11):=1;
BEGIN
a:=length(name);
for i in 1..a
LOOP
IF INSTR(NAME,c,b)>=1 THEN
d:=INSTR(NAME,c,b);
m:=m+1;
b:=d;
END IF;
b:=b+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('count of '||' '||c ||' '||'is'||' '|| m||' '||'in'||' '||name);
end;

Answer / mahesh

select regexp_count('hello','l') from dual;

or

SELECT LENGTH('HELLO')-LENGTH(REPLACE('HELLO','L',NULL)) FROM DUAL;

Answer / ansupriya

SELECT LENGTH(REPLACE(TRANSLATE('HELLO','HEO',' '),' ')) FROM DUAL

Answer / ajit nayak

FUNCTION
---------
CREATE OR REPLACE FUNCTION FN_WORD_CNT (V_NAME VARCHAR2,V_SRCH_WRD VARCHAR2)
RETURN NUMBER
IS

I_COUNT NUMBER;
I_WRD_CNT NUMBER;

TYPE V_WORD IS TABLE OF VARCHAR2(10)
INDEX BY BINARY_INTEGER;
V_S_WORD V_WORD;

BEGIN
I_WRD_CNT := LENGTH(V_NAME);
DBMS_OUTPUT.PUT_LINE(I_WRD_CNT);
I_COUNT := 0;

FOR I IN 1..I_WRD_CNT
LOOP
SELECT SUBSTR(V_NAME,I,1)
INTO V_S_WORD(I)
FROM DUAL;

END LOOP;
FOR I IN 1..V_S_WORD.COUNT
LOOP

IF(V_S_WORD(I) = V_SRCH_WRD) THEN
I_COUNT := I_COUNT + 1;

END IF;
END LOOP;
RETURN I_COUNT;
END;

FOR RUN
-------

select FN_WORD_CNT('AJIT','A')
from dual;

PROCEDURE
---------
CREATE OR REPLACE FUNCTION FN_WORD_CNT (V_NAME VARCHAR2,V_SRCH_WRD VARCHAR2)
RETURN NUMBER
IS

I_COUNT NUMBER;
I_WRD_CNT NUMBER;

TYPE V_WORD IS TABLE OF VARCHAR2(10)
INDEX BY BINARY_INTEGER;
V_S_WORD V_WORD;

BEGIN
I_WRD_CNT := LENGTH(V_NAME);
DBMS_OUTPUT.PUT_LINE(I_WRD_CNT);
I_COUNT := 0;

FOR I IN 1..I_WRD_CNT
LOOP
SELECT SUBSTR(V_NAME,I,1)
INTO V_S_WORD(I)
FROM DUAL;

END LOOP;
FOR I IN 1..V_S_WORD.COUNT
LOOP

IF(V_S_WORD(I) = V_SRCH_WRD) THEN
I_COUNT := I_COUNT + 1;

END IF;
END LOOP;
RETURN I_COUNT;
END;

RUN THIS PROCEDURE
------------------
DECLARE
v_name varchar2(10) := '&name';
v_srch varchar(2) := '&str';
i_cnt number;
begin
DBMS_OUTPUT.PUT_LINE('ENTER NAME '||v_name||CHR(10)||' SEARCH STR '||v_srch);
P_FN_WORD_CNT(v_name,v_srch,i_cnt);
END;

Answer / a.brahmam

select length('HELLO')-length(replace('HELLO','L')) from dual;
ANS:=2

Answer / kuldeep123

select regexp_count('HEllO','L') from dual;

Answer / radhesh

SELECT 'HELLO', INSTR('HELLO','L') AS INSTR FROM dual;

Why partition by is used in sql?

0 Answers  

List the different type of joins?

0 Answers  

Can we update views in sql?

0 Answers  

I have following column in the table. col1 1 a b c 2 3 d and I want to display it as num chars 1 a 2 b 3 c 4 d numbers in one column and letters in another column.

1 Answers   IBM, Saama Tech,

How to start the command-line sql*plus?

0 Answers  

Can a composite key be null?

0 Answers  

What is a column in a table?

0 Answers  

Which one is better sql or oracle?

0 Answers  

Write a query to genarate target column.Please answer me. Advance Thanks. Src Tgt Q10 Quarter to 2010 Q90 Quarter to 1990 Q80 Quarter to 1980 Q74 Quarter to 1974

5 Answers   Infosys,

How to read/write files from pl/sql?

0 Answers  

Can procedure in a package be overloaded?

1 Answers   HCL,

Why is the cursor important?

0 Answers