sqlplus column formating question

[halfbarrel]

Hello,
I have a question about sqlplus. It seems whenever I run and sqlplus and spool out the file the number fields have a preceding space. This is even if I format them like:
column MPI_NBR format 09999999999

It will then look like this in the spool file.
00000001111
Does anyone have any ideas of how to get rid of this.
Thanks,

Chris.

 

[Turkbear]

Hi,
By default, the Spooled Sql will place a space between each field..so you may be seeing that space.To be sure, use Trim(MPI_NBR)AS MPI_NBR
in your query or set colsep to something other than a space and see what happens..

 

[SantaMufasa]

Chris,

The phenomenon you are seeing results from SQL*Plus doing "more" for you than you want or ask for: SQL*Plus allocates an extra character on the left for a "minus sign", just in case your numeric value goes negative.

You can cause your numeric value to appear without SQL*Plus's minus-sign allocation by doing a TO_CHAR() function on your numeric value:

col salary heading "Sal" format a4
select to_char(salary) salary from s_emp;

 Sal
----
2500
1450
940
1200
795
750

But notice the annoying left-justification that occurs. If this is unacceptable, then you can "moosh" the results yet again with this code:

select substr('    '||to_char(salary),-4) salary from s_emp;

 Sal
----
2500
1450
 940
1200
 795
 750

Isn't it silly that we have to go through all this if we use SQL*Plus?

Let us know if this resolves your question(s).

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 21:49 (03Mar05) UTC (aka "GMT" and "Zulu"),
@ 14:49 (03Mar05) Mountain Time

Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[halfbarrel]

Have you guys ever seen it where SQL*Plus put extra spaces in front of a number field so that the field in the spool file is as long as the field's name in the sql.
Thanks for your help,

Chris.

 

[SantaMufasa]

Half,

Not only have we seen it, but that is SQL*Plus's default behaviour.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.