Parsing IIS logs in database for filename

[gcrist]

I'd like to create a join between a column that contains filenames (such as '00001234.pdf') and an IIS inetlog table "target" column which records the path and filename (such as '/Mtracksdoc/topics/ct/00001234.pdf'). The '/Mtracksdoc/topics/' part never changes, but the directory above the file has different length names.

The query I'd eventually like to run would be something like:

select tbdoc.title from tbdoc, inetlog
where tbdoc.docfname ??? inetlog.target

thanks a lot,

greg

 

[gcrist]

Nevermind,

I figured it out, thanks to a posting by Suresh (sgursahaney). For anyone trying to do something similar, REPLACE seems to be the key. It's ugly, but it works. This query removes all the spaces, and directories:

select replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace target,' ',''),'/Mtracksdoc/topics/',''),'pkg/',''),'adv/',''),'eleccomm/',''),'cons/',''),'bills/',''),'buscom/',''),'ct/',''),'custserv/',''),'employee/',''),'industry/',''),'intl/',''),'ntbus/',''),'other/',''),'postal/',''),'pr/',''),'pubs/',''),'retail/',''),'specserv/',''),'stamps/','')
from inetlog

leaving me with a clean list of the filenames i need for my join:

0000C52D.PDF
0000C52D.PDF
0000C52D.PDF
0000BF8C.HTM
0000C54A.HTM
0000C55A.HTM
00009DA9.htm
0000C3AD.HTM
....

-greg