Mysql Substring

Question

Hi everyone, I'm trying to switch to a new mail server, so I want to write a Mysql script to return the table as follows: then export the result as a sql CSV file as the following

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

I want to fine-tune any characters preceding the @ and @ characters in front of the domain name, any ideas will be very useful

 mysql> select email,clear,email AS domain from postfix_users ; +---------------------------+--------+---------------------------+ | email | clear | domain | +---------------------------+--------+---------------------------+ | [e-mail] | passw | [e-mail] | +---------------------------+--------+---------------------------+ 

Answer 1

You can use LOCATE to find the @ position:

  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