Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there an Oracle function that can recognize if a character is readable or not?

I would like to search a specific column for garbled characters. I know that this can typically be done with a regular expression. However, my database contains Japanese characters, so it is not just a simple matter of an [A-Z][0-9] pattern.

share|improve this question
What would constitute an "unreadable character" or a "garbled character"? What is the database character set? – Justin Cave Sep 18 '12 at 1:43
The database character set is UTF-8. I guess garbled character is difficult to defined since it may still appear as japanese character but because it is already corrupted data it would be some squares, diamond and unreadable japanese. – Nap Sep 18 '12 at 1:54
This is impossible to answer unless you list exactly which characters are acceptable. Unprintable characters may be fine on a system with the correct fonts installed. – Phil Sep 18 '12 at 13:11
up vote 2 down vote accepted

I think the answer given the specifics is "no there is not." I would think, however, in Oracle you could use a regular expression perhaps to find fields with characters not in a specific set. See

I don't know how regex usage works with indexes on Oracle however.

share|improve this answer

Check csscan. This tool will tell you which rows in the database are not convertible into particular charset(ISO-LATIN-1 for example).

Then you can also use dump function to dump internal string representation into bytes. Then you can check for presence of bytes of particular range.

3rd possibility would be to implement Java stored procedure and check Character.UnicodeBlock on each string. See How can I detect japanese text in a Java string. It should not be hard to embed such a piece of code Java store proc.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.