Removing non-breaking spaces?

Question

I have a query for remove all special characters.
But ONE space resists to that query at the end of email string.

Example : '[email protected] '

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.

Answer 1

how about whitelisting? ie allow only valid characters

regex_replace [^-_.@a-zA-Z] with ''

Answer 2

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!