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 00005

Solution 1:

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).

Solution 2:

How about multiplication and lpad():

selectlpad(col * 1000, 8, '0')
. . .

Solution 3:

Try the following:

select lpad(5.42562*POWER(10, length(trim(regexp_replace(5.42562, '[^.]+\.(.*)$', '.\1')))-1), 8, '0') from dual;

where 5.42562 would be the column you want.

So basically, you are using Gordon Linoff's answer, but multiplying by 10 powered to the amount of decimals.

Solution 4:

select lpad(rpad(replace('5.95','.',''),4,0),8,0) from dual;

Assuming that either you have scale or not, string has to be suffix with 0 and prefix 0 till 8th digit. I am posting the query.

Let me know if this satisfies your requirement or anything else is required.

select lpad(rpad(replace('5.95','.',''),4,0),8,0) from dual;