Path: g2news2.google.com!news3.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!newsfeed00.sul.t-online.de!newsmm00.sul.t-online.de!t-online.de!news.t-online.com!not-for-mail
From: Maxim Demenko <mdeme...@gmail.com>
Newsgroups: comp.databases.oracle.misc
Subject: Re: character count in a string
Date: Tue, 23 Jan 2007 20:33:35 +0100
Organization: T-Online
Lines: 128
Message-ID: <45B6630F.6030901@gmail.com>
References: <ep56kg$dco$1@news2.netvision.net.il>   <1169567293.582981.200990@s48g2000cws.googlegroups.com>   <1169573280.589094@bubbleator.drizzle.com>   <1169574407.785835.65290@m58g2000cwm.googlegroups.com> <1169577742.055947.293300@d71g2000cwa.googlegroups.com>
Reply-To: mdeme...@google.com
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Trace: news.t-online.com 1169580817 03 23891 SfpzLArLrwJbSa0x 070123 19:33:37
X-Complaints-To: usenet-abuse@t-online.de
To: Charles Hooper <hooperc2...@yahoo.com>
X-ID: XuWM90ZEoe5S3BmOkWEsB5H4Q7rQhjQgSQGvr0pUU7PYgGU0o2tewX
User-Agent: Thunderbird 1.5.0.9 (Windows/20061207)
In-Reply-To: <1169577742.055947.293300@d71g2000cwa.googlegroups.com>

Charles Hooper schrieb:
> 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
>>> damor...@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))
> ------------------
> 9
> 
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
> 

Sorry, could not resist ;-)

SQL> declare
   2  s number;
   3  c number;
   4  begin
   5
   6          s:=dbms_utility.get_time;
   7          for i in 1..100000 loop
   8          SELECT SUM(DECODE(SUBSTR('562225622256222',ROWNUM,1),'2',1,0))
   9          into c
  10          FROM DUAL CONNECT BY LEVEL<=15;
  11          end loop;
  12          s := dbms_utility.get_time -s;
  13          dbms_output.put_line('SUBSTR/DECODE/CONNECT BY time: '|| 
trunc(s/100));
  14
  15          s:=dbms_utility.get_time;
  16          for i in 1..100000 loop
  17          SELECT  SUM(SIGN(INSTR('562225622256222','2',1,ROWNUM)))
  18          into c
  19          FROM DUAL CONNECT BY LEVEL<=15;
  20          end loop;
  21          s := dbms_utility.get_time -s;
  22          dbms_output.put_line('SIGN/INSTR/CONNECT BY time: '|| 
trunc(s/100));
  23
  24          s:=dbms_utility.get_time;
  25          for i in 1..100000 loop
  26          select length('562225622256222') - 
length(replace('562225622256222','2'))
  27          into c
  28          from dual;
  29          end loop;
  30          s := dbms_utility.get_time -s;
  31          dbms_output.put_line('LENGTH/REPLACE time: '|| trunc(s/100));
  32
  33          s:=dbms_utility.get_time;
  34          for i in 1..100000 loop
  35          select length(regexp_replace('562225622256222','[^2]',''))
  36          into c
  37          from dual;
  38          end loop;
  39          s := dbms_utility.get_time -s;
  40          dbms_output.put_line('REGEXP_REPLACE time: '|| trunc(s/100));
  41  end;
  42  /
SUBSTR/DECODE/CONNECT BY time: 17
SIGN/INSTR/CONNECT BY time: 18
LENGTH/REPLACE time: 13
REGEXP_REPLACE time: 13

PL/SQL procedure successfully completed.


Best regards

Maxim