I need to find rows containing non-printable characters in a table. Is there any utility/method to do this in Oracle? The table in which I am searching has quite large number of rows (> 50,000).
There is no utility that Oracle makes which does this for you. You'll likely have to create your own utility. And it would probably be easiest if this was done in PL/SQL. A block such as the following can find any characters in a column which are non-alphabetic characters. It won't take too much work to convert this into a PL/SQL script to perform your needs.
DECLARE strLength NUMBER; colContents VARCHAR2(255); found BOOLEAN:=FALSE; BEGIN SELECT colX INTO colContents FROM tableY WHERE a=b; strLength:=LENGTH(colContents); LOOP -- If not 'a' to 'z', then set flag IF ASCII(SUBSTR(colContents,strLength,1)) < ASCII('a') OR ASCII(SUBSTR(colContents,strLength,1)) > ASCII('z') THEN found:=TRUE; END IF; strLength:=strLength-1; EXIT WHEN strLength=0; END LOOP; IF found THEN DBMS_OUTPUT.PUT_LINE('Contains non-lower case'); ELSE DBMS_OUTPUT.PUT_LINE('Contains only lower case'); END IF; END;
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.