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
while the target table the email column is
I am using MySQL 5. Thanks of any help.
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), '')