Minor formatting problem - not urgent

[Horace]

Hi All,

I've got a cold and can't think straight, so while I'm working on this, I thought I'd post it here as a quick and easy puzzle (hopefully)

I need to output a column, with a leading zero.

Column is PURN NUMBER(10).

I get this:-

select PURN from PERSON
where rownum < 3;

PURN
---------
400000001
400000002

col purn for 0999999999

/

PURN
-----------
0400000001
0400000002

See, it puts a blank before the zero for some reason. Can this be done without resorting to single row functions ?

[Horace]

You don't see the blank on the forum, try it in sqlplus and you'll see what I mean.

It looks like -

PURN
-----------
blank0400000001
blank0400000002

[jmodic]

I hope you understand this is purely SQL*Plus isue - what you are trying to achive will work only in SQL*Plus. For any general solution, you would need to use the proper format mask with TO_CHAR function.

Anyway, if you insist on SQL*Plus with COLUMN formating command, then use this:

col purn for 0999999999MI

Your blank is reserved for a minus sign. With the above setting you are instructing SQL*Plus to display sign at the end of the string. Plus sign is not displayed by default, but if you have negative numbers the minus sign will appear at the right side.

[Horace]

Thanks Jurij,

That did the trick. It was only for a simple report.