Re: character count in a string

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 23 Jan 2007 10:42:22 -0800
Message-ID: <1169577742.055947.293300@d71g2000cwa.googlegroups.com>

Charles Hooper wrote:
> DA Morgan wrote:
> > Charles Hooper wrote:
> > > Eitan M wrote:
> > >> Hello,
> > >> how can I get a specific character count in a string
> > >> (
> > >> i.e : string is 56222, and I am looking for '2' occurance
> > >> when i do :
> > >> select charcount('56222') should return : 3
> > >> )
> > >>
> > >> Thanks :)
> > >
> > > INSTR is all that you need. See:
> > > http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/functions068.htm
> > >
> > > Charles Hooper
> > > PC Support Specialist
> > > K&M Machine-Fabricating, Inc.
> >
> > Given the quality of your responses I am going to have to ask ... how.
> > I think Anurag's response is one solution and this would be mine.
> >
> > SELECT LENGTH(TRANSLATE('56222', '2013456789', '2')) FROM dual;
> >
> > Though I can see numerous creative possibilities using regular
> > expressions, etc.
> > --
> > Daniel A. Morgan
> > University of Washington
> > damorgan_at_x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Group
> > www.psoug.org

> Sorry, I misread the question and do not have an answer. I thought
> that he was looking for the position of the third "2" in a string.

> Ignore this:
> SELECT
> SUM(SIGN(INSTR('562225622256222','2',1,ROWNUM)))
> FROM
> DUAL
> CONNECT BY
> LEVEL<20;

> SUM(SIGN(INSTR('562225622256222','2',1,ROWNUM)))
> -------------
> 9

> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

gazzag suggested SUBSTR, looks like that will work also: SELECT
  SUM(DECODE(SUBSTR('562225622256222',ROWNUM,1),'2',1,0)) FROM
  DUAL
CONNECT BY
  LEVEL<255;

SUM(DECODE(SUBSTR('562225622256222',ROWNUM,1),'2',1,0))

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Jan 23 2007 - 12:42:22 CST