SQL* Plus CLI  «Prev  Next»

Lesson 5Formatting numeric columns
ObjectiveControl the display of numeric columns.

Controlling Numeric Columns Display in Oracle SQL*Plus

Managing the appearance of numeric columns in SQL*Plus is paramount for ensuring clear and comprehensible output, which aids in the effective analysis and interpretation of query results. Oracle SQL*Plus provides robust and flexible mechanisms for formatting the display of numeric columns, including setting the column format, width, and other properties.

Techniques for Controlling Numeric Columns Display

1. Using the COLUMN Command

Definition: The `COLUMN` command in SQL*Plus is used to change the appearance of the data in a particular column.
Syntax: 
COLUMN column_name FORMAT format_model;

Example: To display a numeric column with two decimal places: 
COLUMN salary FORMAT 999,999.99;

In this example, `salary` is the column name and `999,999.99` is the format model.

2. Utilizing Number Format Models

Definition: Number format models allow precise control over the numeric data presentation by defining placeholders, separators, and decimal positions.
Examples:
  1. `9999` displays the number with a maximum of four digits.
  2. `9,999.99` displays the number with comma as the thousand separator and two decimal places.

3. Managing Column Width

Definition: Defining the column width ensures that the output is displayed neatly, preventing undesirable line breaks or misalignment.
Syntax: 
COLUMN column_name FORMAT Awidth;
Example: 
COLUMN employee_id FORMAT A10;
Here, `employee_id` is the column name, and `A10` sets the column width to 10 characters.

4. Using the SET NUMFORMAT Command

Definition: The `SET NUMFORMAT` command is employed to define a default number format for all numeric columns in the query output.
Syntax: 
SET NUMFORMAT format_model;

Example: 
SET NUMFORMAT '9,999.99';
This command sets the default number format to include a comma as the thousand separator and two decimal places.
Mastering the use of the `COLUMN` command, employing appropriate number format models, managing column width, and utilizing the `SET NUMFORMAT` command are essential techniques for effectively controlling the display of numeric columns in SQL*Plus. Properly formatting numeric columns ensures the clarity and usability of the database output, facilitating efficient data analysis and reporting. Adequate attention to these details enhances the overall efficiency and productivity of database operations in Oracle SQL*Plus.
Numeric columns require a different set of formatting characters than those used for text columns. With numeric columns, you not only need to worry about column width, but also about where to put the commas, where to place the decimal point, how to display negative numbers, and other formatting issues.

Numeric formatting characters

The following table shows the different characters that may be used in a numeric format string, and provides examples of their effect on the printed output.

CharacterDescriptionIf you use this format specificationTo display this valueThe output will look like this.
9"9"s are used to represent digits in the output999
999999 		123
123 		123
123
0Same as 9, but does not suppress leading zeros099
099999
990999 		123
123
123 		123
000123
0123
. Marks the decimal point999.99 123.45 123.45
, Marks the location of commas in the output999,999.99 123456.78 123,456.78
$ Places a leading dollar sign in the output$999,999.99 123456.78 $123,456.78
PR Causes negative numbers to display in parentheses(999.99) -123 (123.45)
S Forces a sign to be displayed, even for positive numbers. May be placed at either end of the number.S999
999s 		-123
123 		-123
123+

The following example shows some sample output using three different formats. The SELECT statement selects three values from a table named dual. The values are each formatted differently, so that you can see the effect of the different formats. 

SQL> COLUMN a FORMAT 999,999.99
SQL> COLUMN b FORMAT 099,999.99
SQL> COLUMN c FORMAT $999,999.99
SQL> SELECT 123.45 a, 
  2         234.56 b,
  3         345.67 c
  4         FROM dual;
 A           B            C
----------- ----------- ------------
123.45  000,123.45      $123.45
No matter how you format numbers, SQL*Plus always leaves space for a possible negative value. Thus a numeric column that is formatted as 999 will display four spaces wide to accommodate the potential negative sign.