-1

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

p.campbell
  • 98,673
  • 67
  • 256
  • 322
shubhra
  • 19
  • 1
  • 1
  • 4
    what does non-ascii mean in your context? Values outside the range 0-127? Or do you mean, values which are not "printable ascii" in the range 32 to 127? – Warren P Jul 23 '10 at 14:48
  • 3
    You need to flesh out your question with more details. ASCII is just a mechanism which assigns a numeric value to a character. So, data in a table is ASCII, all the way down. So, is this a question about Unicode? Globalization? Or distinguishing regular ASCII from extended ASCII sets? – APC Jul 23 '10 at 14:51
  • 2
    Attention downvoters! There is at least the kernel of an interesting and worthwhile question here. Please give @shubhra the chance to refine the question. – APC Jul 23 '10 at 15:08
  • Is it particular tables/columns or do you somehow need to derive the tables/columns ? Are you including CLOBs or just VARCHAR2/CHAR 9or NCHAR/NVARCHAR2). – Gary Myers Jul 24 '10 at 07:07

1 Answers1

0

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.

Tom
  • 4,742
  • 25
  • 32