Re: character count in a string

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 23 Jan 2007 20:33:35 +0100
Message-ID: <45B6630F.6030901@gmail.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
>>> 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))
> ------------------
> 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 Received on Tue Jan 23 2007 - 13:33:35 CST