How to detect and replace non-printable characters from table?

Question

I have imported data from xls file into table. but there are some garbage (non ascii charactors).

I want to remove those non printable characters from database.

here is the query i found which can select the entries which has non-ascii characters

 select * from TABLE where COLUMN regexp '[^ -~]';

But how can i remove those characters from table using mysql query or procedure ?

Please give suggestions.

thanks in advance.

Non-[ASCII](http://en.wikipedia.org/wiki/ASCII) (e.g. [kanji](http://en.wikipedia.org/wiki/Kanji)) is not the same as [non-printable](http://en.wikipedia.org/wiki/Control_character) (e.g. [ESC](http://en.wikipedia.org/wiki/Escape_character) and [BEL](http://en.wikipedia.org/wiki/Bell_character)). Which are you seeking to remove? — eggyal, Jun 28 '12 at 11:32
It seems you cannot. Look at this [possible duplicate question](http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql). You may also want to refer to [MySQL documentation on REGEXP](http://dev.mysql.com/doc/refman/5.1/en/regexp.html) — Dil, Jun 28 '12 at 12:28
actually when i am view table i see square bracket in column. when i copy that square bracket and paste in notepad it show [sub] in black background block. how to remove that ? — Prabhu, Jun 28 '12 at 12:34
Check that you are using the right encoding in notepad. Also check that you are using the right encoding when transferring from xls to database. — Dil, Jun 28 '12 at 12:41
What was the character was in that position in the original XLS file? — eggyal, Jun 28 '12 at 12:42

score 2 · Answer 1 · answered May 16 '22 at 08:03

Since the question is about "detect and replace" I wouldn't suggest the Delete query from @TheWitness. Instead I would do something like this:

UPDATE some_table SET some_column = REGEXP_REPLACE(some_column, '[^ -~]', '') WHERE some_column  REGEXP '[^ -~]'

The query above will use regular expression to search for the particular characters and with REGEXP_REPLACE it will replace them with empty string.

2 Answers2