MySQL Help - Stripping a piece of a string

wonder_lander · 5 Mar 2013 at 22:35

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

These should all return.com

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

These should be .com.au

Any pointers guys?

Ross1234 · 5 Mar 2013 at 22:47

You can find all .co.uk like so

Code:

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

That would be the simplest way.

Randomface74 · 5 Mar 2013 at 22:50

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

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

wonder_lander · 5 Mar 2013 at 22:52

Ross1234 said:
You can find all .co.uk like so

Code:

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 · 5 Mar 2013 at 22:53

billysielu said:
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.

Ross1234 · 5 Mar 2013 at 23:05

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

Redrum · 6 Mar 2013 at 00:59

Ross1234 said:
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

This ^.

The only proper way I can think of to do this is to store a list of extensions to reference against, since that's the only way of knowing whether you want to go one or two levels deep. If for some reason you can't add a new table and it has to be done in the SQL, then if you know all the extensions that might be there, you could construct an SQL with a list of extension and use string manipulation like this:

PHP:

select 
if(
reverse(left(reverse(domain),locate(".",reverse(domain)))) 
in 
(".com",".net",".org",".us"),
reverse(left(reverse(domain),locate(".",reverse(domain)))),
if(reverse(left(reverse(domain),locate(".",reverse(domain),locate(".",reverse(domain))+1))) 
in
 (".co.uk",".au.com", ".org.uk","gov.us"), reverse(left(reverse(domain),locate(".",reverse(domain),locate(".",reverse(domain))+1))),"unknown")

) as domain_extension from domain;

But it would be much better to just store the extensions somewhere.

wonder_lander · 6 Mar 2013 at 08:12

Ross1234 said:
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!