How to Insert Leading Zeros in Oracle

The Oracle "LPad" function lets you insert any leading character to the beginning of your database field data. Use this function to include leading zeros in your table fields. You can add one or several leading zeros in front of the value. You can use this function when you want to standardize data length in a table field.

Instructions

1. Open the Oracle Enterprise Manager software or your preferred PL-SQL editor from the Windows program menu. Open the SQL editor and log in to your Oracle database server.

2. Create the string that you want to store in the database and pad with leading zeros. The following code creates an Oracle variable:

   declare myvalue varchar(10);
   myvalue := '999';

3. Add the leading zeros to the variable data using the LPad function. The following code adds three zeros to the beginning of the "myvalue" variable:

   myvalue := lpad(myvalue, 3, '0');

   Replace "3" with the number of zeros you wish to insert.

References

• Tech on the Net: Oracle/PLSQL: LPad Function