How to Insert Padding Zeroes in Oracle

Oracle PL/SQL provides a number of powerful shortcuts to avoid the need for adding additional code. Often you're required to format numbers and characters in Oracle to present data in different formats or currencies. One of these facilities is called the Oracle "Format Model." This allows fields of variable length to be displayed or used in a specific format. Number fields can be formatted in a number of ways, including zeroes to pad a number before and after the actual number.

Instructions

1

Prefix numbers with zeroes before the actual number. Numbers are usually held in the database as "Number" fields. Therefore they will need to be converted into the either a date field (for dates) or a character field so that the padding format can be applied to the number. The following example selects a number field and using the format model prefixes the number with zeroes:

SELECT * from customer where customerid = TO_CHAR('12345','0999');

2

Add zeroes after the actual number. This ensures no trailing blanks exist and the field is populated entirely with digits. The following is an example of using trailing zeroes with a number:

SELECT * from customer where customerid = TO_CHAR('12345','9990');

3

Update, delete or select numbers or date format fields using the format model and padding with zeroes as appropriate. A number of other formats can be used in addition to padding data with zeroes. These can be found in the Oracle documentation for your system.