Maxim Demenko wrote:
> 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
You have far too much time on your hands. Can I get you a glass of scotch?
--
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
Received on Tue Jan 23 2007 - 14:44:37 CST
