Add Leading Characters to a String in Oracle SQL

by

To add leading characters or zeros to a string you can use the lpad() function in Oracle SQL.

LPAD() Syntax

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
       FROM DUAL;

Output:

********100,000

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
   FROM DUAL;

Output:

0000000002

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;

Output:

*******893

See also:

Leave a Comment