Formatting Character Strings
SQL*Plus offers only one format element when it comes to character strings. That element is “A”. “A” is always followed by a number specifying the column width in characters. Character strings shorter than the column width are displayed left-justified within the column. Character strings that exceed the column width are either wrapped or truncated based on the option specified in the COLUMN command. The following example shows a text column that has been formatted wide enough to display the entire character string:
SQL>COLUMN a FORMAT A40
SQL>SELECT 'An apple a day keeps the doctor away.' A
2FROM dual;
A ---------------------------------------- An apple a day keeps the doctor away.
You can format the column so that it is 18 characters wide, which results in the text being wrapped within that space:
SQL>COLUMN a FORMAT A18
SQL>SELECT 'An apple a day keeps the doctor away.' A
2FROM dual;
A ------------------ An apple a day kee ps the doctor away .
By default, SQL*Plus wraps the text right in the middle of a word, if necessary. You can use the WORD_WRAPPED option of the COLUMN command to wrap text only at word boundaries.
SQL>COLUMN a FORMAT A18 WORD_WRAPPED
SQL>SELECT 'An apple a day keeps the doctor away.' A
2FROM dual;
A ------------------ An apple a day keeps the doctor away.
You also have the ability to truncate text at the column boundary.
SQL>COLUMN a FORMAT A18 TRUNCATE
SQL>SELECT 'An apple a day keeps the doctor away.' A
2FROM dual; ...
Get Oracle SQL*Plus: The Definitive Guide now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.