SQL – Methods of Reformatting into Equivalent Forms 3

2 12 2009

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

Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 144 other followers

%d bloggers like this: