How to find the count of letter "L" in HELLO
Answers were Sorted based on User's Feedback
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;
Is This Answer Correct ? | 4 Yes | 0 No |
Answer / mahesh
select regexp_count('hello','l') from dual;
or
SELECT LENGTH('HELLO')-LENGTH(REPLACE('HELLO','L',NULL)) FROM DUAL;
Is This Answer Correct ? | 4 Yes | 1 No |
Answer / ansupriya
SELECT LENGTH(REPLACE(TRANSLATE('HELLO','HEO',' '),' ')) FROM DUAL
Is This Answer Correct ? | 1 Yes | 1 No |
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;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / a.brahmam
select length('HELLO')-length(replace('HELLO','L')) from dual;
ANS:=2
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / radhesh
SELECT 'HELLO', INSTR('HELLO','L') AS INSTR FROM dual;
Is This Answer Correct ? | 1 Yes | 8 No |
What are the PL/SQL Statements used in cursor processing ?
how do you login to mysql using unix shell? : Sql dba
what is the difference between truncate and drop statements? : Sql dba
What is the difference between left and left outer join?
Can we use pl sql in mysql?
what are different types of keys in sql?
can we delete the trigger in a view? if yes why if not why?
What are all the different normalization?
what is a database? : Sql dba
What is java sql connection?
What is dml with example?
Can we join tables without foreign key?