Re: Help with complicated select statement

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Wed, 11 Jan 2006 23:20:25 +0000
Message-ID: <ma4bs1l2m6f6fhecgg90hvigsfeh8i0adi@4ax.com>

On 11 Jan 2006 08:03:02 -0800, "No Such Luck" <no_suchluck_at_hotmail.com> wrote:

>I have a table column, "filename", that stores the names of different
>types of files gathered from the web. Because the files are gathered
>from the web, some of the file names have URL encoded characters, i.e.,
>%20, %22, etc.
>
>I'd like a select statement to scan the data in the column, dynamically
>create the list of hex codes that occur, and tally the number of each
>occurrence.
>
>Something like:
>
>Hex Code: Tally
>%20 17,809
>%22 1,295
>%7E 57
>
>etc.
>
>- A file name may have 0, 1 or N hex codes
>- The hex code is guranteed to be 3 characters long (i.e., %, followed
>by 2 chars)
>
>I've tried using REGEXP_LIKE and the like, but haven't had much luck so
>far.

 Since you're trying to do some looping over data within a single row to produce multiple rows, it's a good candidate for a pipelined table function.

 Here's an example:

SQL> select * from t;

FILENAME

---

test

test%20%20
test%20%20%7e
test%22
test%22%2etest

 [ So, that's: 4 x %20, 1 x %7e, 2 x %22, 1x %2e. ]

SQL> create or replace type t_hex_char as object (hex_char varchar2(3))   2 /

Type created.

SQL> create or replace type t_hex_char_table as table of t_hex_char   2 /

Type created.

SQL> create or replace function extract_urlencoded_hex_chars   2 return t_hex_char_table pipelined
  3 as

  4      v_pos binary_integer;
  5      v_hex varchar2(3);
  6  begin
  7      /* For all rows in the table */
  8      for r_file in (select filename from t)
  9      loop
 10          v_pos := instr(r_file.filename, '%');
 11  
 12          /* Search for the leading % character */
 13          while (v_pos > 0)
 14          loop
 15              /* Extract the % and the two following characters */
 16              v_hex := substr(r_file.filename, v_pos, 3);
 17  
 18              /* Check the two characters after the % are both
 19               * 0-9 or A-F - i.e. it forms a hexadecimal number */
 20              if (substr(v_hex, 2, 1)        between '0' and '9' or
 21                  upper(substr(v_hex, 2, 1)) between 'A' and 'F')
 22              and (substr(v_hex, 3, 1)        between '0' and '9' or
 23                  upper(substr(v_hex, 3, 1)) between 'A' and 'F')
 24              then
 25                  /* Output a row */
 26                  pipe row (t_hex_char(v_hex));
 27              end if;
 28  
 29              /* Move to the next occurrence of % */
 30              v_pos := instr(r_file.filename, '%', v_pos + 1);
 31          end loop;
 32      end loop;
 33      return;

 34 end;
 35 /

Function created.

SQL> select hex_char
  2 from table(extract_urlencoded_hex_chars);

HEX

---
%20
%20
%20
%20
%7e
%22
%22
%2e

8 rows selected.

SQL> select hex_char, count(*) tally
  2  from   table(extract_urlencoded_hex_chars)
  3  group by hex_char;

HEX      TALLY
--- ----------
%7e          1
%22          2
%20          4
%2e          1

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Received on Wed Jan 11 2006 - 17:20:25 CST