Path: news.easynews.com!core-easynews!newsfeed1.easynews.com!newsfeed2.easynews.com!easynews.com!easynews!newsfeed.news2me.com!newsfeed-west.nntpserver.com!hub1.meganetnews.com!nntpserver.com!headwall.stanford.edu!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: postbus@sybrandb.demon.nl (Sybrand Bakker)
Newsgroups: comp.databases.oracle.tools
Subject: Re: need help with Oracle (for a query)
Date: 5 May 2003 07:13:43 -0700
Organization: http://groups.google.com/
Lines: 25
Message-ID: <a20d28ee.0305050613.698a5fc2@posting.google.com>
References: <ZXqta.91802$iy5.2854423@twister2.libero.it>
NNTP-Posting-Host: 145.13.42.4
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1052144023 3847 127.0.0.1 (5 May 2003 14:13:43 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 5 May 2003 14:13:43 GMT
Xref: core-easynews comp.databases.oracle.tools:58789
X-Received-Date: Mon, 05 May 2003 07:13:11 MST (news.easynews.com)

"Limner" <ATTENZIONEpend77@yahoo.itATTENZIONE> wrote in message news:<ZXqta.91802$iy5.2854423@twister2.libero.it>...
> Hi to all
> 
> i've a question.
> I need to know how to count how many times it present a particular caracter
> in a varchar2 field ( i've oracle 9i )
> 
> For example:     " Pippo|pluto|paperino"  and i want to know how many times
> is present the caracter "|"    ( 2 times )
> 
> can someone help me in this ??
> 
> Thanks in advance
> 
> Danilo

select length(translate('abc.de.f','.abcdef','x')) from dual
does the trick for periods: you translate the . by a character you
don't use and all other characters translate to the NULL string.


Hth

Sybrand Bakker
Senior Oracle DBA