numeric format models - sql


Elite Guru
Dec 30, 2006
so i fixed my laptop (thanks keefy), so its time for another highly entertaining thread......

can anyone help me with format masks in sql please? its really pissing me off that i don't understand fully how they affect the output in oracle (or any database software).

for example:

select to_char(0001,'009') from dual;
would return 001?

the 0's represent what, just leading zeros that are added onto the figure? and the 9's represent the width of the number?

what confuses me then is when you put decimals in.

select to_char(0011,'009.9') from dual;
why does this then return 11.0 and not 1.1?

can anyone explain this to me simply? because oracle docs tends to gloss over certain aspects when you try them out in reality.


CETPA Infotech

The exact numeric data types are SMALLINT , INTEGER , BIGINT , NUMERIC(p,s) , and DECIMAL(p,s) . Exact types mean that the values are stored as a literal representation of the number's value.