CODEWITHSUNDEEP
mysqlnon-ascii-characterssystem-stored-procedures
Prabhu
Prabhu

Reputation: 188

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

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.

Upvotes: 2

Views: 5030

Answers (2)

Ale
Ale

Reputation: 1032

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.

More on REGEXP_REPLACE

Upvotes: 2

TheWitness
TheWitness

Reputation: 1

It's fairly simple, you just change your SELECT into a DELETE as follows

DELETE FROM TABLE WHERE COLUMN regexp '[^ -~]';

Upvotes: -1

Related Questions