MySQL Help - Stripping a piece of a string

[wonder_lander]

I'm looking to extract the domain extension from a table full of website addresses, can anyone give me some pointers.

Here are some possible permutations and the part I want to extract:

These should all return .co.uk

• domainname.co.uk
• www.domainname.co.uk
• http://domainname.co.uk
• http://www.domainname.co.uk

These should all return.com

• domainname.com
• subdomain.domainname.com
• www.domainname.com
• domainname.uk.com

These should be .com.au

• domainname.com.au
• www.domainname.com.au
• http://www.domainname.com.au

Any pointers guys?

 

[wonder_lander]

You can find all .co.uk like so

select * from the_table_name where website_address like '%.co.uk'

That would be the simplest way.

The trouble is the number of different extension it could be but it's certainly a solution!.

http://en.wikipedia.org/wiki/List_of_Internet_top-level_domains

 

[wonder_lander]

SQL has to be one of the harder ways of doing this.

http://msdn.microsoft.com/en-us/library/system.uri.aspx

Unfortunately I'm not a coder so am just looking at how best to do this with the raw data that's in the database I have.

 

[wonder_lander]

If it were me, and I had to do it this way I would just create a lookup table of every extension then use that in a "like" query against my other table.


For example:
http://sqlfiddle.com/#!2/c949f/3/0

That sounds feasible and will allow me to add extra detail into the extensions table.

Thanks for the help guys!