Re: find out how many times particular character occur in given string.

On Wed, 10 Dec 2008 05:19:20 -0800, Sanjeev wrote:

> Dear Gurus,
>
> I want to find out how many times particular character occur in
> given string.
> e.g. 'A*B*C*D*E*F' count of '*' is 5
> 'A*B*C*D' count of '*' is 3
> 'A' count of '*' is 0
>
> Is there any function available in SQL to find out the above requirement
> ?
>
> Could anyone help me in above?
>
> Thanking in advance
> Sanjeev

SQL> select regexp_count('A*B*C*D*E*F','\*') as "Reply for C.D.O.S"   2 from dual;

Reply for C.D.O.S

                5

Elapsed: 00:00:00.01

You can also do that in Perl:

perl -e '$a="A*B*C*D*E*F"; print scalar(split(/\*/,$a))-1,"\n";'

SQL is not an ideal tool for the string manipulation.

-- 
Mladen Gogala
http://mgogala.freehostia.com