Horizontal tab characters can cause formatting errors in Oracle output because computer operating systems do not always look at tabs the same. With a Unix OS, the presence of a tab means "move to the right until the current column is a multiple of 8." For Windows and Mac OS X, a tab is interpreted as a multiple of 4. Custom tab configurations sometimes set tabs to multiples of 2. Replacing “chr(9)” -- the ASCII code for a tab -- with a set number of spaces ensures the format of Oracle output remains consistent no matter which operating system you or others use.
Write a standard SQL SELECT statement to generate output that reveals whether your content contains tab spaces. The SELECT statement uses a wildcard character to ensure the statement checks all the records in a table, a WHERE filtering clause and the LIKE operator as a matching parameter. For example, if you want to display as output and then examine all tab instances in an Oracle customer information table, write the SELECT statement as:
SELECT * from customer_info WHERE column LIKE chr(9);
Write another SQL command starting with telling Oracle that you want to update information in the customer_info table. Start the statement with an UPDATE command:
Tell Oracle to remove and replace all tab instances in each column of the customer_info table with a specific number of spaces -- three spaces in this example -- by adding an Oracle-specific, PL/SQL function to your UPDATE statement. As an alternative you can omit the “space” instruction, but Oracle will then remove the tabs and replace them with nothing.
SET column = REPLACE(column,chr(9),' ', ' ', ' ');
View the new output:
SELECT * from customer_info;
- Photo Credit Medioimages/Photodisc/Photodisc/Getty Images