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;

Is big data nosql?

0 Answers  

Does normalization improve performance?

0 Answers  

What are sql data types?

0 Answers  

State few characteristics of pl/sql?

0 Answers  

what is 'force view'?

3 Answers   Cap Gemini, SPIC,

Let us suppose we have a table with structure in order empno empname empdesig empcountry and now i want to re-organize the columns of this table to empno empdesig empname empcountry how can i do this with queries ? assume that table contains the data.

3 Answers  

Is left join same as inner join?

0 Answers  

What are the built in functions of sql?

0 Answers  

What company owns postgresql?

0 Answers  

Can we join two tables without common column?

0 Answers  

What is the difference between syntax error and runtime error?

0 Answers  

What is input buffer in sql*plus?

0 Answers