Mysql TRIM/RTRIM/REPLACE doesn’t work to remove trailing spaces from an email column

I have this insert select statement, copying data from employee bulk add staging to employee table:

INSERT INTO employee (tr_email) 
SELECT TRIM(eb.tr_email)
  FROM employee_bulkadd_staging eb
  ON DUPLICATE KEY UPDATE     
  tr_email=(SELECT IF(eb.tr_email = '' or eb.tr_email IS NULL, employee.tr_email,TRIM(eb.tr_email)));

Even if I change it to RTRIM or REPLACE(eb.tr_email, ‘ ‘, ”) to replace the spaces with nothing it will still not modify the data to be inserted. I cannot get my login to work since my backend application detects these trailing white spaces at the end of the email address.

Sample email value with spaces is like this, highlighted to show the spaces:

The email address column from the source table, the data type is varchar(100) utf8_general_ci

while the target table the email column is varchar(100) latin1_swedish_ci

I am using MySQL 5. Thanks of any help.

Answer

I think you are facing this problem due to CHAR(10) in your text. Can you try removing it –

REPLACE(eb.tr_email, CHAR(10), '')

Advertisement

Answer