December 2, 2009
(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)
A couple of years ago someone asked in the comp.databases.oracle.misc Usenet group the following question:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/3572ee04308f6a27
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 )
This message thread, like several others, generated suggestions from several people.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Anurag Varma suggested the following:
ORA92> select length('56222') - length(replace('56222','2')) from dual;
LENGTH('56222')-LENGTH(REPLACE
------------------------------
3
10GR2> select length(regexp_replace('56222','[^2]','')) from dual;
LENGTH(REGEXP_REPLACE('56222','[^2]',''))
-----------------------------------------
3
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DA Morgan suggested the following:
SELECT LENGTH(TRANSLATE('56222', '2013456789', '2')) FROM dual;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I then offered the following:
SELECT
SUM(SIGN(INSTR('562225622256222','2',1,ROWNUM)))
FROM
DUAL
CONNECT BY
LEVEL<20;
SUM(SIGN(INSTR('562225622256222','2',1,ROWNUM)))
-------------
9
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
SELECT
SUM(
CASE WHEN INSTR('562225622256222','2',1,ROWNUM)>0 THEN
1
ELSE
0
END
)
FROM
DUAL
CONNECT BY
LEVEL<20;
SELECT
COUNT(
CASE WHEN INSTR('562225622256222','2',1,ROWNUM)>0 THEN
1
ELSE
NULL
END
)
FROM
DUAL
CONNECT BY
LEVEL<20;
SUM(CASEWHENINSTR('562225622256222','2',1,ROWNUM)>0THEN1ELSE0END)
9
COUNT(CASEWHENINSTR('562225622256222','2',1,ROWNUM)>0THEN1ELSENULLEND)
9
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Maxim Demenko offered the following to test the performance of each method:
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
Recent Comments