Home » SQL & PL/SQL » SQL & PL/SQL » procedure to find out how many times a perticular character is present in a ginen string
procedure to find out how many times a perticular character is present in a ginen string [message #264952] Wed, 05 September 2007 01:52 Go to next message
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 #264957 is a reply to message #264952] Wed, 05 September 2007 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
to find out the same at run time

What do you mean? SQL is executed by at run time!
Quote:
also used for other strings

Use a variable instead of a constant.

Regards
Michel
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 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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 Go to previous messageGo to next message
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 #264982 is a reply to message #264957] Wed, 05 September 2007 03:26 Go to previous messageGo to next message
axiom
Messages: 26
Registered: August 2007
Junior Member
also used for other string means
when i pass other string other than welcome
Re: procedure to find out how many times a perticular character is present in a ginen string [message #264984 is a reply to message #264982] Wed, 05 September 2007 03:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
did you by any chance copy the "2" at the start of line 2?
Re: procedure to find out how many times a perticular character is present in a ginen string [message #264987 is a reply to message #264982] Wed, 05 September 2007 03:35 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"Welcome" has nothing to do with it; it is a hyphen (-) sign at the end of the first line; try to modify it into
SQL> SELECT LENGTH('&input_string')
  2       - LENGTH(REPLACE('&input_string', '&search_string', '')) result
  3  FROM dual;
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 Go to previous messageGo to next message
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>
Re: procedure to find out how many times a perticular character is present in a ginen string [message #265010 is a reply to message #265005] Wed, 05 September 2007 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just a slight change in Littlefoot's function
SQL> CREATE OR REPLACE FUNCTION fun_cnt_str 
  2    (par_input IN VARCHAR2, par_search IN VARCHAR2)
  3    RETURN NUMBER
  4  IS
  5  BEGIN
  6    RETURN LENGTH(par_input) - 
  7           NVL(LENGTH(REPLACE(par_input, par_search, '')),0);
  8  END;
  9  /

Function created.

SQL> SELECT FUN_CNT_STR('JJJJJ','J') FROM DUAL;
FUN_CNT_STR('JJJJJ','J')
------------------------
                       5

1 row selected.

Regards
Michel
Re: procedure to find out how many times a perticular character is present in a ginen string [message #265014 is a reply to message #265005] Wed, 05 September 2007 04:30 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

Add a NVL function outside the length
i.e.
SELECT LENGTH('&input_string')
       - nvl(LENGTH(REPLACE('&input_string', '&search_string', '')),0) result
  FROM dual;

This is happen because when you pass the same characters the replace function returns null and so length also returns null and for that result returns null also.

Cheers
Soumen
Re: procedure to find out how many times a perticular character is present in a ginen string [message #265047 is a reply to message #265014] Wed, 05 September 2007 05:43 Go to previous messageGo to next message
axiom
Messages: 26
Registered: August 2007
Junior Member
I GOT THE EXACT RESULT WHAT I WANT

SO THANKU VERY MUCH YOU ALL
AGAIN THANKU VERY VERY MUCH
Re: procedure to find out how many times a perticular character is present in a ginen string [message #265053 is a reply to message #265047] Wed, 05 September 2007 05:54 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, I understand you're happy, but - switch off CapsLock key, please.
Re: procedure to find out how many times a perticular character is present in a ginen string [message #265099 is a reply to message #265053] Wed, 05 September 2007 07:26 Go to previous messageGo to next message
axiom
Messages: 26
Registered: August 2007
Junior Member
U r not the littlefoot but smartfoot
Re: procedure to find out how many times a perticular character is present in a ginen string [message #265102 is a reply to message #265099] Wed, 05 September 2007 07:36 Go to previous message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, because I've read the OraFAQ Forum Guide. Read it - you'll learn what is an acceptable message on OraFAQ Forum, how to write it, format it, along with some other helpful information.
Previous Topic: Oracle Objects
Next Topic: Package Security....
Goto Forum:
  


Current Time: Fri Dec 02 12:29:24 CST 2016

Total time taken to generate the page: 0.08566 seconds