Mysql子串

Question

大家好，我正在尝试迁移到新的邮件服务器，所以我想编写 Mysql 脚本来返回如下表然后将结果导出为 CSV 文件sql语句如下

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

我想在域名之前对@ 和@ 符号本身之前的任何字符进行子字符串任何想法都会有很大的帮助

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

+---------------------------+--------+---------------------------+
| email                     | clear  | domain                    |
+---------------------------+--------+---------------------------+
| user@domain.tld           | passw  | user@domain.tld           |
+---------------------------+--------+---------------------------+

Answer 1

您可以使用 LOCATE 找到 @: 的位置

 LOCATE('@',email)

所以要找到域:

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