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.

CodePudding user response：

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), '')

CodePudding user response：

I've coded your data and can't reproduce the problem of spaces.
Is it a problem with a carriage return or newline character: char(11) and/or char(13) as Ankit suggests? I've added markers < and > to make it clearer where there are whitespace characters.

CREATE TABLE employee (
    id int not null auto_increment primary key,
    tr_email varchar(100) 
    collate latin1_swedish_ci
    )
    ;
CREATE TABLE employee_bulkadd_staging(
    id int not null auto_increment primary key,
    tr_email varchar(100) 
    collate utf8_general_ci
    )
    ;

insert into employee_bulkadd_staging (tr_email)
values ('[email protected]   
');

insert into employee (tr_email)
select trim(REPLACE(tr_email,'
','')) from employee_bulkadd_staging

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)))

select 
concat('<',tr_email,'>' )
,
REPLACE(concat('<',tr_email,'>' ), '
', '')r
from employee_bulkadd_staging eb

concat('<',tr_email,'>' ) | r                   
:------------------------ | :-------------------
<[email protected]   <br>>  | <[email protected]   >

select id,concat('<',tr_email,'>') ebs from
 employee_bulkadd_staging;
 
select id, concat('<',tr_email,'>' ) emp 
 from employee;

id | ebs                     
-: | :-----------------------
 1 | <[email protected]   <br>>

id | emp                     
-: | :-----------------------
 1 | <[email protected]>       
 2 | <[email protected]   <br>>

db<>fiddle here