Add Leading Characters to a String in Oracle SQL

Add Leading Characters to a String in Oracle SQL

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • SQL
  • 2 mins read

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: