To add leading characters or zeros to a string you can use the
lpad() function in Oracle SQL.
lpad(expr1, n, expr2)
LPAD will return
expr1 with the characters from
expr2 appended to the left to make it a total of n characters in length. This method can be used to reformat the results of a query.
Add Leading Characters to a String in Oracle SQL Example
The following SQL query will add a leading asterisk (*) to the given number as string:
SELECT LPAD('100,000',15,'*') protected_amt
The supplied string length was 7 for the above query so 8 asterisks (*) were added at the left.
Add Leading Zeros to a String
To add leading zeros you just need to change the expr2. Below is an example:
SELECT LPAD('2',10,'0') led_zeros
Using LPAD() Function in PL/SQL Program
The following PL/SQL program adds the asterisks to the number variable:
declare n_amt number := 893; begin dbms_output.put_line(lpad(n_amt, 10, '*')); end;