unwanted leading blank space on oracle number format

Question

I need to pad numbers with leading zeros (total 8 digits) for display. I'm using oracle.

select to_char(1011,'00000000') OPE_NO from dual;
select length(to_char(1011,'00000000')) OPE_NO from dual;

Instead of '00001011' I get ' 00001011'. Why do I get an extra leading blank space? What is the correct number formatting string to accomplish this?

P.S. I realise I can just use trim(), but I want to understand number formatting better.

@Eddie: I already read the documentation. And yet I still don't understand how to get rid of the leading whitespace.

@David: So does that mean there's no way but to use trim()?

Answer 1

To understand number formatting better I suggest reading the Number Format Models in the Oracle documentation.

Answer 2

From that same documentation mentioned by EddieAwad:

Negative return values automatically contain a leading negative sign and positive values automatically contain a leading space unless the format model contains the MI, S, or PR format element.

---

EDIT: The right way is to use the FM modifier, as answered by Steve Bosman. Read the section about Format Model Modifiers for more info.

Answer 3

Use FM (Fill Mode), e.g.

select to_char(1011,'FM00000000') OPE_NO from dual;