I have a query for remove all special characters.
But ONE space resists to that query at the end of email string.
Example : 'test@hotmail.com '
UPDATE my_table SET email= REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(LTRIM(RTRIM(email))),\'\x0B\',\'\'),\'\0\',\'\'),\'\t\',\'\'),\'\r\',\'\'),\'\n\',\'\'),\'\r\n\',\'\'),\'\n\r\',\'\'),\' \',\'\'),CHAR(160),\'\') WHERE id=X
Why?
I use this statement because I have a WHERE id IN(), so I don't want to process special characters in PHP. I want to UPDATE every emails directly with SET and replace, trim() function.
However, some whitespace is not deleted and I don't know why.
My table has approximately 12 millions of rows. I have programmed a CRON which fetch them to delete all specials characters (unfortunately because in the past we don't had check them on INSERT).
So I have build this query to process my 12 MM rows. It works very great except the right whitespace (sometimes it is removed sometimes not). And I want to add that on Workbench, the query works 100% all the time. It does not make sense.
Here is my query again without backslash and with my where IN:
UPDATE NEWSLETTER_SUBSCRIPTION SET email= REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(LTRIM(RTRIM(email))),'\x0B',''),'\0',''),'\t',''),'\r',''),'\n',''),'\r\n',''),'\n\r',''),' ',''),CHAR(160),'') WHERE id IN (' . implode(',', $idEmailToBeProcess) . ')
$idEmailToBeProcess contains around 500 ids.
I think the right whitespace it's a non-breaking space, but my last test with CHAR(160) in my query didn't work.
Ok, finally I had found the problem !!!
Encoding of PDO is the problem...
Just adjusted driver options and all works good!
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\'')
Thanks guys anyway!