CODEWITHSUNDEEP
mysqlsqltld
Cyclone
Cyclone

Reputation: 18295

Detect TLD in email address via SQL query

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        [email protected]
2      bill         [email protected]

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.

Upvotes: 1

Views: 1931

Answers (3)

Toby
Toby

Reputation: 226

Use REGEXP,

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

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

Upvotes: 2

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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.

Upvotes: 0

Josh
Josh

Reputation: 992

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

Upvotes: 2

Related Questions