Search code examples
sqloracle-databaseoracle-data-integrator

How to pad zeroes for a number field?

How can I pad an integer with zeros on the left (lpad) and padding a decimal after decimal separator with zeros on the right (rpad).

For example: If I have 5.95 I want to get 00005950 (without separator).

Solution

  • If you want the value up to thousandths but no more of the decimal part then you can multiply by 1000 and either FLOOR or use TRUNC. Like this:

    SELECT TO_CHAR( TRUNC( value * 1000 ), '00000009' )
FROM   table_name;

    or:

    SELECT LPAD( TRUNC( value * 1000 ), 8, '0' )
FROM   table_name;

    Using TO_CHAR will only allow a set maximum number of digits based on the format mask (if the value goes over this size then it will display #s instead of numbers) but it will handle negative numbers (placing the minus sign before the leading zeros).

    Using LPAD will allow any size of input but if the input is negative the minus sign will be in the middle of the string (after any leading zeros).