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 do you mean by field in sql?
Name three sql operations that perform a sort.
Why we use sql profiler?
how to rename an existing table in mysql? : Sql dba
If i can use sys.check_constraints to display my constraints from my database using sql server 2005, how can i display then if i am using sql server 2000????
query to retrive the employees whose sal is greater than avg sal
How can we schedule the procedure to run automatically ?
What is difference between joins and union?
what is a materialized view? : Sql dba
What does joining a thread mean?
What program will open a mdb file?
What is a primary key example?