Select part of column via regex in a MySQL DB

Question

I have a table with e-mailaddresses, which are all like redir.IDENTIFIER@redirthis.url

How can I run a query which only selects the identifier?

Table emails

+----+--------------------------------+
| id | email                          |
+----+--------------------------------+
|  1 | [email protected]     |
|  2 | [email protected]     |
|  3 | [email protected] |
+----+--------------------------------+

Expected/desired result

+----+------------+
| id | email      |
+----+------------+
|  1 | A73283     |
|  2 | XAAX83     |
|  3 | A73283F3GH |
+----+------------+

Abhik Chakraborty · Accepted Answer · 2014-05-30 07:44:04Z

1

You can use substring_index for this if the pattern is same

mysql> select substring_index(substring_index('[email protected]','@',1),'.',-1);
+-----------------------------------------------------------------------------+
| substring_index(substring_index('[email protected]','@',1),'.',-1) |
+-----------------------------------------------------------------------------+
| A73283                                                                      |
+-----------------------------------------------------------------------------+

So the query would be

select 
id,
substring_index(substring_index(email,'@',1),'.',-1) as email
from emails

answered May 30, 2014 at 7:44

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

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

Collectives™ on Stack Overflow

Select part of column via regex in a MySQL DB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related