COLUMN

Syntax

COLUMN [<name>]; -- Displays configuration

COLUMN <name> <command>; -- Changes configuration

Description

Displays the formatting settings for the <name> column or changes the formatting options for the <name> column.

<command> can be a combination of one or more of the following options:

Command Description
ON Enables formatting settings for the relevant column.
OFF Disables formatting settings for the relevant column.
FORMAT <format>

Sets the <format> format string for the values of the relevant column. Formatting options are dependent on the type of column. Therefore, format strings for text columns should not be used on numeric columns. Numeric values are right-aligned and other data types left-aligned. Format strings for alphanumeric columns have the form a<num>, where <num> indicates the number of letters. The width of a single column cannot be greater than the maximum length of a row.

Example: a10 formats a column of 10.

Format strings of numbers consist of the elements '9', '0', '.' (point) and 'EEEE'. The width of the column results from the length of the specified format string.

  • 9 - At this position, one digit is represented if it is not a NULL before or after the decimal point.
  • 0 - At this position, one digit is always represented, even if it a NULL before or after the decimal point.
  • .(point) - Specifies the position of the point. The decimal point is represented as a point.
  • EEEE - The number is represented in scientific notation with exponents. Exactly four Es must be contained in the format string, because the width of the column is extended by four characters due to the exponents.

The following rules apply: 

  • There must be at least one nine or zero.
  • The decimal point is optional and it only may occur once.
  • If there is a decimal point, a nine or zero must be both before and after it.
  • Left to the decimal point nines are only allowed before zeros and right to it vice versa.
  • The four E’s are optional and may occur only once at the end in the format. If this scientific notation is chosen, numbers will be normalized to one digit to the left of the decimal point.
  • All characters are case-insensitive.

Example: 99990.00 formats a number to the width of eight places (seven digits plus the decimal point). There will be a display of at least one digit or up to five digits to the left and exactly two digits to the right of the decimal point. In the case of the number zero, for example, 0.00 will be shown, and in case of 987.6543, it will be printed as 987.65, since it is mathematically rounded .

A # is displayed for each digit if a value is not presentable because it requires too many places to the left of the decimal point.
CLEAR Deletes the formatting options for the relevant column.
JUSTIFY <LEFT|RIGHT|CENTER> Defines the alignment of the column name.
LIKE <column> Copies all formatting options from the specified column.
WORD_WRAPPED The values of the relevant column will, if possible, be wrapped between the individual words.
WRAPPED The values of the relevant column will be wrapped at the right margin of the column.
TRUNCATED The values of the relevant column will be trimmed at the right margin of the column.
NULL <text> NULL values of the relevant column will be displayed as <text>.
HEADING <text> Sets a new heading for the relevant column.
ALIAS <text> Sets an alias name for the relevant column.

The following "COLUMN" SQL*Plus statements are not supported. However, for reasons of compatibility they will not generate a syntax error:

  • NEWLINE
  • NEW_VALUE
  • NOPRINT
  • OLD_VALUE
  • PRINT
  • FOLD_AFTER
  • FOLD_BEFORE

Examples

SQL_EXA> column A;
COLUMN A ON
FORMAT 9990

SQL_EXA> column A format 90000.0;
SQL_EXA> column b format 99990;
SQL_EXA> column b just left;
SQL_EXA> select a,a as b from ty;

Results

A B
0011.0 11
0044.0 44
0045.0 45
0019.0 19
0087.0 87
0099.0 00
0125.0 33
0442.0 442