parse results in MySQL via REGEX

Question

Hi,

I'm a bit confused on the functionality of the REGEX support for MySQL and I have yet to find a solid example on how to separate a result with REGEX within an sql statement.

Example:

How could I pull data from a table emails that looks something like...

+-------------------------+
|Emails                   |
|-------------------------|
|[email protected]|
+-------------------------+

and return something through an sql statement that looks like...

+------------------------------+
|Username   |  Domain    | TLD |
|-----------|------------|-----|
|some.email | yourdomain | com |
+------------------------------+

Answer 1

MySQL doesn't have built-in functionality to do what you're asking for. It would be possible by defining some new functions, but it's probably easier to just do it in whatever programming language you're accessing the database through.

For something as simple as an email address though, you shouldn't need to use regular expressions at all, you can use the SUBSTRING_INDEX() function, as:

SELECT
    SUBSTRING_INDEX(email, '@', 1) AS Username,
    SUBSTRING_INDEX(SUBSTR(email, LOCATE('@', email)), '.', 1) AS Domain,
    SUBSTRING_INDEX(email, '.', -1) AS TLD
FROM users;

In words, that's:

• Username = everything before the first '@'
• Domain = everything between the first '@' and the first '.'
• TLD = everything after the last '.'

Answer 2

You can also try,

select substring_index(email, '@', 1) username
       ,substring_index(substring_index(email, '@', -1), '.',1) domain
       ,substring_index(email, '.', -1) tld
from 
( select '[email protected]' email ) E

Uses only substring_index. 1st substring_index everything to the left of '@', 3rd (inner substring_index) to the right of '@' which then parsed by 2nd substring to get domain, and finally last one pulls everything to the right of '.'

HTH, hiregion.com