Selecting emails by domain (TLD) from MySQL table?

hman · January 9th, 2008, 01:52 PM

Hi,

I have a table with a large number of emails (10000+). I need to extract specific emails based on their domain extension (TLD). For example I want to extract any .uk domains only from the table. How can I do this

Your help would be much appreciated.

urtrivedi · January 10th, 2008, 12:42 AM

SELECT * where REVERSE(LEFT(REVERSE(email),LOCATE('.',REVERSE(ema il)) - 1)) ='uk' FROM table

above query will give you list of emails ending with .uk

Also refer to
http://dev.mysql.com/doc/refman/5.0/...-functions.htm

urt

hman · January 11th, 2008, 04:32 PM

Quote:

quote:Originally posted by urtrivedi

SELECT * where REVERSE(LEFT(REVERSE(email),LOCATE('.',REVERSE(ema il)) - 1)) ='uk' FROM table

above query will give you list of emails ending with .uk

Also refer to
http://dev.mysql.com/doc/refman/5.0/...-functions.htm

urt

Hi urt,

I tried your query changing the table name, but it seems to throw an error?
I am running it on MySql 4.1 in MySQLadmin.

Code:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where REVERSE( LEFT( REVERSE( email ) , LOCATE( '.' , REVERSE( email ) ) - 1 ) )' at line 1

Please advise

Ian