Removing non-breaking spaces?

Question

I have a request to delete all special characters.
But one space supports this request at the end of an email line.

Example: ' [e-mail] '

 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 operator because I have a WHERE id IN() , so I don't want to handle special characters in PHP. I want UPDATE each letter to be directly using the SET and replace , trim() functions.

However, some spaces are not removed, and I do not know why.

My table contains about 12 million rows. I programmed a CRON that extracted them to remove all special character characters (unfortunately, because in the past we did not test them for INSERT ).

So, I created this query to handle my 12mm strings. It works great, except for the right space (sometimes it is sometimes deleted). And I want to add that on Workbench the request runs 100% all the time. It does not make sense.

Here is my query again without a 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 about 500 identifiers.

I think right spaces are inextricable space, but my last test with CHAR(160) in my query did not work.

Answer 1

Ok, finally I found the problem !!!

PDO encoding - problem ...

Just configured driver parameters and everything works fine!

 PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\'') 

Thanks to everyone guys!

Answer 2

how about whitelisting? those. allow only valid characters

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