string functions 6

[sweetleaf]

Hi,

in 8i is there a function that will tell me how many occurences of a character there are in a string:

eg. how many comma's in "12,3,45,99"?

Thanks

 

[lewisp]

If your column will only have numbers and commas, an expression to calculate the number could be

[tt]SELECT Length(Translate('your_string',',0123456789',',')) no_of_commas
FROM sys.dual;[/tt]

 

[SantaMufasa]

Sweet,

Here is the function that I believe you want:

create or replace function how_many (search_str in varchar, whole_str in varchar)
	return number is
begin
	return (length(whole_str)-Length(replace(whole_str,search_str,null)))/
		length(search_str);
end;
/

Here is the invocation using the sample string you posted:

col x heading "How|Many|Commas" format 99
select how_many(',','12,3,45,99') x from dual;
   How
  Many
Commas
------
     3

Here is an invocation against a set of table values:

col "a's" format 999
select last_name,how_many('a',last_name)"a's" from s_emp
where rownum <= 5
LAST_NAME        a's
--------------- ----
Garcia             2
Ngao               1
Nagayama           4
Quick-To-See       0
Ropeburn           0

It will even tell you how many occurrences there are of multi-character strings:

col x heading "How|Many|'efg'|strings" format 999
select how_many('def','abcdefgabcdefg123123')x from dual
    How
   Many
  'efg'
strings
-------
      2

Is this what you wanted?

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:53 (03Mar04) UTC (aka "GMT" and "Zulu"), 11:53 (03Mar04) Mountain Time)

 

[BJCooperIT]

Very innovative solution Mufasa. Have a star!

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8i & 9i - Windows 2000

 

[sweetleaf]

Mufasa,

thats excatly what i needed, thanks big time!

 

[lewisp]

I am humbled in the presence of a genius. Have one on me too.

 

[SantaMufasa]

Barbara, "Sweet", and Lewis,

You are all way to kind. By no means is it "genius"; it's just a lazy guy looking for the easiest way out of doing work.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:29 (03Mar04) UTC (aka "GMT" and "Zulu"), 12:29 (03Mar04) Mountain Time)

 

[BJCooperIT]

For those, like me, that like to comment how these "little miracle" functions work:

/*==================================================*/
/* Function to return the number of times that a    */
/* search string is found inside a text string      */
/*                                                  */
/* Example:                                         */
/* select HOW_MANY('AN','MAN CAN STAND') from DUAL; */
/*                        --  --   --               */
/* Returns 3 (three 'AN's in 'MAN CAN STAND')       */
/*                                                  */
/* Calculated:                                      */
/*   Length of text                                 */
/*   - (Length of text w/search string removed)     */
/*      divided by the length of the search text    */
/*   (13 - 7) / 2 = 3                               */
/*==================================================*/

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8i & 9i - Windows 2000

 

[sem]

One more star from me! Excellent idea!

Regards, Dima

http://seminihin.narod.ru

 

[carp]

At the risk of piling on, me too!

 

[avjoshi]

Alright, now I can't just watch every body giving stars and not acknowledge Dave's excellent post. A star from me to!!

Anand