Page 1 of 1

my substring() function does not work

Posted: Sun Feb 15, 2009 10:23 pm
by bulgin
I have a table web2_access_log with a field request_uri that always has a uri as such in it:

/universe.jpg
/world.jpg
/stunning.jpg

I also have a cust database with file_names and email_addresses as such, for example:

file_names email_addresses
universe.jpg tom@yahoo.com
world.jpg jackie@hotmail.com
stunning.jpg toni@msn.com

I'm trying to get the matches between the data with the following command, and it works if I first manually remove the forward slash from each record in the web2_access_log field, but not with the following syntax:

SELECT substring( web2_access_log.request_uri
FROM 1 ), cust.file_names, cust.email_addresses
FROM web2_access_log
JOIN cust ON substring(web2_access_log.request_uri
FROM 1) = cust.file_names

I'm stumped and would appreciate any help that a guru could might spare.

Thanks!

Re: my substring() function does not work

Posted: Mon Feb 16, 2009 12:08 am
by requinix
The request URI and a file name are different ideas. One is what the client asked from your server, one is an object on the filesystem.
Store the request URI and the file name separately. Or you can use one and not the other - across the entire application.

Re: my substring() function does not work

Posted: Mon Feb 16, 2009 10:29 am
by bulgin
It turns out that the following snippet of code worked. I don't know what you are talking about, tasairis, I'm only trying to compare and join two tables with file names, not retrieve or work on the actual file:

Code: Select all

SELECT substring( web2_access_log.request_uri,2 ), cust.file_names, cust.email_addresses
FROM web2_access_log
JOIN cust ON substring(web2_access_log.request_uri,2) = cust.file_names
what I had before which didn't work was:

Code: Select all

SELECT substring( web2_access_log.request_uri
FROM 1 ), cust.file_names, cust.email_addresses
FROM web2_access_log
JOIN cust ON substring(web2_access_log.request_uri
FROM 1) = cust.file_names
even if I changed the "1" to a "2" in the above code it still didn't work. But now it does using the first snippet of code, above.