How to select exploded column value in sql

Question

I know, this could easly be done using PHP, but just curious,

How could I explode on mail field to select the value from alias column with the first exploded part by @?

Current:

+-------+-----------------------------------------+
| alias | mail                                    |
+-------+-----------------------------------------+
|       | [email protected]                      |
|       | [email protected]                  |
+-------+-----------------------------------------+

Desired:

+-------+------------------------------------------------+
| alias        | mail                                    |
+-------+------------------------------------------------+
| user.one     | [email protected]                      |
| user.two.foo | [email protected]                  |
+-------+------------------------------------------------+

In pseudo code would be like

update tablename set tablename.alias = explode('@', tablename.mail)[0];

Answer 1

In MySQL, you can use substring_index():

update t
    set alias = substring_index(t.mail, '@', 1);