I'm trying to build a select statement that lists the "root" urls in the database (www.blahblahblah.com,org,etc.). So far, I've got:

SELECT * FROM Links WHERE URL LIKE "http://www.%.___";

Unfortunately, this gives me addresses like www.blah.blah.blah.com. Instead of the "%", how would I tell it I want everything between the first "." and the second "." so that I only get root domain names?

Any help would be very much appreciated.

Thanks,
Sean

I'm not sure about with other databases, but with MySQL, you can go:
SELECT * FROM Links WHERE URL REGEXP '^http://www\.[^.]+\.[a-z]{3}/'
Of course this regex also has some problems...
like it will only match URL's that have / at the end (www.foo.com/), but if you take it off then www.foo.bar.com also matches.
Then there's also www.foo.com.uk won't match.
etc

Adrian

That gives me what I need. Thanks for your quick assistance!

Sean

It has been a while since you gave me this select statement, but I just recently started using it. Unfortunately, it doesn't JUST give me the root URLs like http://www.foo.com/. It also gives me URLs like http://www.foo.com/bar/index.html. Not sure why I get full URLs like that. Any ideas?

BTW: I am using MySQL.

Sean

Try:

SELECT * FROM Links WHERE URL REGEXP '^http://www\.[^.]+\.[a-z]{3}/$'

Was missing a $ sign I believe. May not be to quick though.

Cheers,

Alex
--
Gossamer Threads Inc.

How about:

SELECT * FROM Links WHERE URL REGEXP '^http://[^/]+/?$'

Both seem to work. Thanks for the help!

Sean