parse results in MySQL via REGEX

Question

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 |
+------------------------------+

Chad Birch · Accepted Answer · 2010-03-09 22:30:37Z

10

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 '.'

edited Mar 9, 2010 at 22:30

answered Mar 9, 2010 at 22:12

Chad Birch

75k24 gold badges155 silver badges150 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chad Birch Over a year ago

My mistake, the arguments to LOCATE() are backwards, I'll edit my answer to fix it.

Andrew Barber · Accepted Answer · 2013-02-21 02:07:31Z

0

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 '.'

edited Feb 21, 2013 at 2:07

Andrew Barber

40.3k20 gold badges97 silver badges124 bronze badges

answered Mar 17, 2010 at 20:08

Shiva

6512 gold badges6 silver badges10 bronze badges

Collectives™ on Stack Overflow

parse results in MySQL via REGEX

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related