OraFAQ Forum: SQL & PL/SQL » any build-in function to count the appearences of a character in a string

Home » SQL & PL/SQL » SQL & PL/SQL » any build-in function to count the appearences of a character in a string

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

any build-in function to count the appearences of a character in a string [message #2501]

Sun, 21 July 2002 07:24

keane
Messages: 23
Registered: June 2002

Junior Member

Hi,
I want to count how many '#' in string 'a#b#c#', so I do the following,
size := 1;
while INSTR('a#b#c#', '#', 1, size) > 0 LOOP
size := size + 1;
end loop;
size := size - 1;
I think it works. Do you know any build-in function can do it quickly? Or there are better ways to do it?
Thanks a lot.

keane

Report message to a moderator

Re: any build-in function to count the appearences of a character in a string [message #2507 is a reply to message #2501]

Mon, 22 July 2002 10:57

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

The easiest way to to substract the length of the string with the character removed from the original length of the string. For example, to count the occurances of 'E':

sql>select object_type, 
  2         nvl(length(object_type), 0) - 
  3           nvl(length(replace(object_type, 'E')), 0) number_of_e
  4    from all_objects
  5   group by object_type;
 
OBJECT_TYPE        NUMBER_OF_E
------------------ -----------
CONSUMER GROUP               1
CONTEXT                      1
FUNCTION                     0
INDEX                        1
INDEXTYPE                    2
JAVA CLASS                   0
JAVA RESOURCE                2
LIBRARY                      0
OPERATOR                     1
PACKAGE                      1
PROCEDURE                    2
SEQUENCE                     3
SYNONYM                      0
TABLE                        1
TYPE                         1
VIEW                         1

In PL/SQL, this would be:

v_size := nvl(length(v_string), 0) - nvl(length(replace(v_string, v_character)), 0);

Report message to a moderator

Re: any build-in function to count the appearences of a character in a string [message #2519 is a reply to message #2501]

Tue, 23 July 2002 01:22

Deepa
Messages: 269
Registered: November 2000

Senior Member

hi,
you can do it using replace and length function as in the following sql statement.
sql statement:
select
length('a#b#c#')-length(replace('a#b#c#',#,null))
from dual;

hope this helps u.

regards,
deepa

Report message to a moderator

Previous Topic:	Expecting trigger to be called explicitly
Next Topic:	SQL*Loader troubles

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 02:09:12 CDT 2024