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:
- Convert the first letter in uppercase in Oracle SQL.
- Convert to Lowercase in Oracle SQL
- Remove Spaces from Left in Oracle SQL
- Pad Characters Right Side of a String in Oracle