Using REPLACE with CHAR(160) is Returning Hexadecimal as Value

I am trying to get rid of &nbsp characters in MYSQL, but am getting weird behavior where using REPLACE is returning a hexadecimal string.

The original value is some HTML stored in a field with the type BLOB:

<h3>This was just an appetizer. Are you ready for the full course?</h3><p>Dive into more business news, check out the latest tech trends, and get a couple quick tips from our health section.  </p></div>

​x
 
<h3>This was just an appetizer. Are you ready for the full course?</h3><p>Dive into more business news, check out the latest tech trends, and get a couple quick tips from our health section.  </p></div>​

The SQL I am using is this:

UPDATE tbl
SET field = REPLACE(field, CHAR(160), '');

 
UPDATE tblSET field = REPLACE(field, CHAR(160), '');​

And after executing, this is what is left in the database:

3C68333E5468697320776173206A75737420616E206170706574697A65722E2041726520796F7520726561647920666F72207468652066756C6C20636F757273653F3C2F68333E3C703E4469766520696E746F206D6F726520627573696E657373206E6577732C20636865636B206F757420746865206C61746573742074656368207472656E64732C20616E6420676574206120636F75706C6520717569636B20746970732066726F6D206F7572206865616C74682073656374696F6E2E20C23C2F703E3C2F6469763E

 
3C68333E5468697320776173206A75737420616E206170706574697A65722E2041726520796F7520726561647920666F72207468652066756C6C20636F757273653F3C2F68333E3C703E4469766520696E746F206D6F726520627573696E657373206E6577732C20636865636B206F757420746865206C61746573742074656368207472656E64732C20616E6420676574206120636F75706C6520717569636B20746970732066726F6D206F7572206865616C74682073656374696F6E2E20C23C2F703E3C2F6469763E​

What is going on and how could I avoid this? Do I need to use VARCHAR for the field type?

Answer

I found that CHAR codes didn’t work, but a copy pasted whitespace worked. This looks like a normal space, but is in fact CHAR(160) and I don’t have an error anymore. ' '

Advertisement

Answer