Mysql substring

Question

Hi all i'm trying to migrate to a new mail server so i want to wrote Mysql script to return a table as the following then export the result as CSV file sql statement as the following

`select email,clear,email AS domain from postfix_users `

i want to substring any characters preceding the @ and the @ symbol iteself before the domain name any ideas would be great assist

mysql> select email,clear,email AS domain from postfix_users ;

+---------------------------+--------+---------------------------+
| email                     | clear  | domain                    |
+---------------------------+--------+---------------------------+
| [email protected]           | passw  | [email protected]           |
+---------------------------+--------+---------------------------+

Answer 1

You can use LOCATE to find the position of the @:

 LOCATE('@',email)

So to find the domain:

SELECT CASE 
    WHEN LOCATE('@',email) = 0 THEN ''
    ELSE SUBSTRING(email,LOCATE('@',email)+1)
    END as Domain
FROM YourTable