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.

  • 1
    What would constitute an "unreadable character" or a "garbled character"? What is the database character set? Sep 18, 2012 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, 2012 at 1:54
  • 1
    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, 2012 at 13:11

2 Answers 2


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 http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm

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


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 stored procedure.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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