How to Replace Tabs From Oracle Output

Save

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:

    UPDATE customer_info

  • 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;

References

  • Photo Credit Medioimages/Photodisc/Photodisc/Getty Images
Promoted By Zergnet

Comments

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!