Detect TLD in email address via SQL query

Question

I need to detect the TLD on a large database of users' email addresses via SQL query.

Email addresses are stored as a field in a row. It's organized kindof like:

id     username     email

1      steve        steve@place.com
2      bill         bill@fake.asdf

I'm trying to detect if the TLD doesn't match "com" or "org" or "net", and ONLY return those values from the database which do not match those particular TLDs. Obviously in this case, only the user with id 2 will be fetched.

This has to occur in the SQL query.

How large is the users' table? – ypercubeᵀᴹ Jun 08 '11 at 22:25 — ypercubeᵀᴹ, Jun 08 '11 at 22:25
Which RDBMS? (MySQL, Oracle, SQL-Server, ...) – ypercubeᵀᴹ Jun 08 '11 at 22:26 — ypercubeᵀᴹ, Jun 08 '11 at 22:26
@ypercube Few thousand members, and mysql. – Cyclone Jun 08 '11 at 22:59 — Cyclone, Jun 08 '11 at 22:59

score 2 · Answer 1 · edited Jun 08 '11 at 23:21

2

Use REGEXP,

SELECT fields FROM table WHERE email REGEXP '^(net|org|com)$';

You can edit the expression to get the desired result. :)

edited Jun 08 '11 at 23:21

GSerg

76,472
17
159
346

answered Jun 08 '11 at 22:14

Toby

226
1
11

It looks like you'll probably want to remove that "^" – thomasrutter Apr 20 '16 at 05:41

score 2 · Answer 2 · answered Jun 08 '11 at 22:22

Well, there's probably a better way to do this in your particular system, but this doesn't use any DB-specific functions. Plus, if you decide to read the exclude-list from a table at some point instead of hard-coding it, this lends itself to ready modification.

select users.*
from users
left join (
  select 'com' as tld
  union all select 'net'
  union all select 'org'
) tlds on users.email like '%.' || tld
where tlds.tld is null

ypercubeᵀᴹ · Answer 3 · 2011-06-08T23:12:21.067

0

SELECT id
FROM users
WHERE SUBSTRING_INDEX( email, '.', -1 )
        NOT IN ('com', 'net', 'org')

However you run this (it's easy to use LIKE instead), it's not going to be fast. If you want to have queries that search the TLD of the emails (with thousands or millions of rows in the table) and the queries to be fast, you can add a tld field in the table and an index on that field.

edited Jun 08 '11 at 23:12

answered Jun 08 '11 at 23:04

ypercubeᵀᴹ

113,259
19
174
235

Detect TLD in email address via SQL query

3 Answers3