How to remove hex characters from DB?

Question

Hi, i have a MySQL DB where the rows have characters like %20, %2C, etc... how can i remove them from the DB without editing the file on notepad? The .sql file is about 300mb in size... thanks

Answer 1

What database?

Do you want to replace codes with their proper characters; like %20 with ' ' (space)?

You may need to look at exactly how your text is escaped, but you can naively do something using builtin string functions:

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_replace

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_unhex

UPDATE tbl
SET col = REPLACE(col, '%20', UNHEX('20'))
WHERE col LIKE '%\%20%';

Also, I would take measures to ensure that this kind of data does not get into the database in whatever insert/import mechanism was used.