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;

Explain the difference between 'between' & 'and' operators in sql

0 Answers  

column A column b | output 10 7 | 10 5 8 | 8 7 -9 | 7 3 5 | 5 0 6 | 6 Write a sql query to print such output.

4 Answers  

What is pl sql collection?

0 Answers  

What is sql and db2?

0 Answers  

Can I call a procedure inside a function?

0 Answers  

How do I view a procedure in sql?

0 Answers  

What is normalization sql?

0 Answers  

How many types of index are there?

0 Answers  

Is big data nosql?

0 Answers  

What are the features of pl sql?

0 Answers  

Which one of the following join types will always create a Cartesian Product? 1. CROSS JOIN 2. LEFT OUTER JOIN 3. RIGHT OUTER JOIN 4. FULL OUTER JOIN 5. INNER JOIN

2 Answers  

What is Raise_application_error ?

1 Answers