Remove non-ASCII values from Oracle table

Question

How can you remove non-ASCII values from a table in Oracle with PL/SQL?

Answer 1

Assuming that you have a table with a VARCHAR2 column that contains some non-alphanumeric characters, then you can replace them with an SQL statement. You might start with something like this and refine it to suit your needs:

UPDATE mytable x
   SET x.col = REGEXP_REPLACE(x.col, '[^[:alnum:] ]', ' ')
 WHERE REGEXP_LIKE (x.col, '.*[^[:alnum:]].*')

This statement uses regular expressions in an attempt to replace all the non-alphanumeric characters with spaces. You will probably need to make adjustments if you want to leave other desired characters, like commas, in place.

Of course, if your needs are massively more complicated than replacing a few characters in a couple of columns then you might need to take a different approach. If this is the case, then perhaps you could expand your question to give more information about your specific problem.