Section B.2. Formatting Character Strings

B 2 Formatting Character Strings

SQL*Plus offers only one format element when it comes to character strings: A. A is always followed by a number specifying the column width in characters . Character strings shorter than the column width are displayed as left-justified within the column. Character strings that exceed the column width are wrapped or truncated based on the option specified in the COLUMN command. The following example shows a text column 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

2

FROM dual;

A

----------------------------------------

An apple a day keeps the doctor away.

You can format the column so 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

2

FROM 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

2

FROM 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

2

FROM dual;

A

------------------

An apple a day kee

When used with the ACCEPT command, a character format defines the maximum number of characters SQL*Plus will accept from the user :

SQL>

ACCEPT some_text CHAR FORMAT A10 thisthatthen

"thisthatthen" does not match input format "A10"

 

SQL>

Although the character format used with ACCEPT specifies a maximum length, it doesn't specify a minimum length. You can enter fewer characters than the format calls for, even to the point of entering nothing at all.

     

Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements



Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151

Similar book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net