procedure to find out how many times a perticular character is present in a ginen string [message #264952] |
Wed, 05 September 2007 01:52 |
axiom
Messages: 26 Registered: August 2007
|
Junior Member |
|
|
hi all experts
SQL> select length('welcome')-length(translate('welcome','we','w')) from dual;
LENGTH('WELCOME')-LENGTH(TRANSLATE('WELCOME','WE','W'))
-------------------------------------------------------
2
the above statement results no of 'e' present in welcome
now i want to write a procedure to find out the same at run time & also used for other strings
|
|
|
|
Re: procedure to find out how many times a perticular character is present in a ginen string [message #264962 is a reply to message #264952] |
Wed, 05 September 2007 02:11 |
|
Littlefoot
Messages: 21821 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Well, I'd rather use another approach; LENGTH is to be used, but with the REPLACE function instead:
SELECT LENGTH('&input_string') -
LENGTH(REPLACE('&input_string', '&search_string', '')) result
FROM dual;
Enter value for input_string: littlefoot
Enter value for input_string: littlefoot
Enter value for search_string: t
RESULT
----------
3
SQL> If you want to move it to a procedure (a function would probably be a better choice), change substitution variables with a function IN parameters.
CREATE OR REPLACE FUNCTION fun_cnt_str
(par_input IN VARCHAR2, par_search IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
RETURN LENGTH(par_input) -
LENGTH(REPLACE(par_input, par_search, ''));
END;
SQL> SELECT fun_cnt_str('littlefoot', 't') result FROM dual;
RESULT
----------
3
|
|
|
Re: procedure to find out how many times a perticular character is present in a ginen string [message #264980 is a reply to message #264962] |
Wed, 05 September 2007 03:20 |
axiom
Messages: 26 Registered: August 2007
|
Junior Member |
|
|
thanku very much littlefott
but the first one is not executing
it shoe error
SQL> SELECT LENGTH('&input_string') -
> LENGTH(REPLACE('&input_string', '&search_string', '')) result
2 FROM dual;
Enter value for input_string: littlefoot
Enter value for input_string: littlefoot
Enter value for search_string: t
old 1: SELECT LENGTH('&input_string') LENGTH(REPLACE('&input_string', '&search_string', '')) result
new 1: SELECT LENGTH('littlefoot') LENGTH(REPLACE('littlefoot', 't', '')) result
SELECT LENGTH('littlefoot') LENGTH(REPLACE('littlefoot', 't', '')) result
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
|
|
|
|
|
|
Re: procedure to find out how many times a perticular character is present in a ginen string [message #265005 is a reply to message #264962] |
Wed, 05 September 2007 04:13 |
axiom
Messages: 26 Registered: August 2007
|
Junior Member |
|
|
THANKU
CREATE OR REPLACE FUNCTION fun_cnt_str
(par_input IN VARCHAR2, par_search IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
RETURN LENGTH(par_input) -
LENGTH(REPLACE(par_input, par_search, ''));
END;
SQL> SELECT fun_cnt_str('littlefoot', 't') result FROM dual;
RESULT
----------
3
THE ABOVE FUNCTION WORKINIG FINE ]
BUT WHEN I PASS FOLLOWING THEN IT WILL NOT WORK
WHY THIS IS HAPPENING PLEASE GIVE ME SOLUTION FOR SAME
SQL> SELECT FUN_CNT_STR('JJJJJ','J') FROM DUAL;
FUN_CNT_STR('JJJJJ','J')
------------------------
SQL>
|
|
|
|
|
|
|
|
|