p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   MySQL (http://p2p.wrox.com/forumdisplay.php?f=99)
-   -   Selecting emails by domain (TLD) from MySQL table? (http://p2p.wrox.com/showthread.php?t=65147)

hman January 9th, 2008 01:52 PM

Selecting emails by domain (TLD) from MySQL table?
 
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



All times are GMT -4. The time now is 11:52 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.