Help with complicated select statement

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.

Thanks for any direction. Received on Wed Jan 11 2006 - 10:03:02 CST