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 |
+---------------------------+--------+---------------------------+
| user@domain.tld | passw | user@domain.tld |
+---------------------------+--------+---------------------------+
解决方案
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