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;

Do ddl statements need commit?

0 Answers  

how to decrement dates by 1 in mysql? : Sql dba

0 Answers  

How do I get sql certification?

0 Answers  

Does sap use sql?

0 Answers  

I have a tablle like this: cust acc ----------- a 1 b 2 b 3 c 4 c 5 c 6 I Want below o/p: cust acc --------------- a 1 b 2|3 c 4|5|6 Please any one can you have any ideas share me. I have urgent requirement.

7 Answers   MTS,

Name the different types of indexes in sql and define them.

0 Answers  

What will you get by the cursor attribute sql%found?

0 Answers  

what are the differences between procedure-oriented languages and object-oriented languages? : Sql dba

0 Answers  

what are enums used for in mysql? : Sql dba

0 Answers  

what does the t-sql command ident_incr does? : Transact sql

0 Answers  

Can one improve the performance of sql*loader? : aql loader

0 Answers  

What are the different ddl commands in sql?

0 Answers