Q
Problem solve Get help with specific problems with your technologies, process and projects.

Finding rows with non-printable characters in them

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


Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close