Re: character count in a string

Maxim Demenko wrote:
> 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

Nice test!

SUBSTR/DECODE/CONNECT BY time: 5
SIGN/INSTR/CONNECT BY time: 5
LENGTH/REPLACE time: 1
REGEXP_REPLACE time: 1

The above test results are from Oracle 10.2.0.2 running on Windows 2003 x64

Inefficiency of SQL is not a problem, it just means that you need a bigger CPU :-)

Actually, the test proves a valid point - just because it works, does not mean that it should be used. Thanks for taking the time to build the test.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Jan 23 2007 - 14:17:24 CST