To get "http://www.google.com/"; out of the URL, you can do this: LEFT( referer, LENGTH( SUBSTRING_INDEX( referer, '/', 3 ) ) + 1 )
If you don't care about the trailing slash, you can use just the SUBSTRING_INDEX() portion:
SUBSTRING_INDEX( referer, '/', 3 )
Using the LENGTH() function just helps guarantee that you'll get the trailing slash if you want it. The MySQL manual doesn't specify what happens if the count value you feed SUBSTRING_INDEX() exceed the count of the delimiter, but it looks kind of like it just returns back the whole string.
Wes
On Jul 20, 2004, at 10:11 PM, <[EMAIL PROTECTED]> wrote:
What I am trying to do is select the hostname out of a refering url. Such as:
http://www.google.com/search?hl=en&ie=UTF -8&q=%22Third+Park+Point+Beach+Water+Contact+Advisory+Posted%3B+May+Ind icate+Long-Term+Problem%22&btnG=Google+Search
All I really want to get is: http://www.google.com/
So I have:
Select DISTINCT LEFT(referer, LOCATE('/',referer)-1) referer , count(*) as refCount
FROM NNtracking
WHERE referer != ''
GROUP BY referer
Limit 10
but that only selects me: http:/
I have tried a couple of REGEXP ^/ variations but either I am on the wrong track or I can't get the syntax right.
Advice?
--ja --
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
