How To Use LPAD In Oracle

This help is based on examples so it would be easier to understand. Oracle LPAD function allows to format an output of a SQL query or a PL/SQL code. The function fills the extra space in output with defined characters from left side. The padding character by default is “white space”. The syntax of Oracle Lpad is :

LPAD (<string>, <nr_of_length> [,<padding_string>]);

To go through the Oracle Lpad examples we need to know the string length. The text we are going to use is “Oracle DBA” and to get the length size we will use Oracle Length function. You can find the length amount below.

 SELECT  length('Oracle DBA')
   FROM dual;

The output of the last query shows 10 characters. To pad the text with an extra character we will set Oracle Lpad length to 11 and the output shows the “Oracle DBA” string starting with a gap in front of it. Oracle Lpad fills by default the extra length with white-space.

 SELECT lpad ('Oracle DBA',11)
   FROM dual;

The second Oracle Lpad example is same as the last one only in this time we will define our own character and this will be star (*). You will see in the output a *-character in front of the “Oracle DBA” string. Now you may wonder why there is only 1 star – we did declare the length to 11 character again. The Oracle Lpad fills only the extra places left after the string with the Lpad characters.

 SELECT lpad ('Oracle DBA',11,'*')
   FROM dual;

The next Oracle Lpad query has set length to 6 character that is less than the input string length. Take a look at the SQL query output below. The “Oracle DBA” has become to “Oracle” and there is no star character on the left side. Oracle Lpad also will truncate your input string if it doesn’t fix into the length size and there is no stars because no extra space has available. The input string doesn’t fit into 6 character.

 SELECT lpad ('Oracle DBA',6,'*')
   FROM dual;

The Lpad example below is one of the ways to format the SQL query output. The Select statement is written by using Oracle Dual table and the CONNECT BY hierarchical operator to generate 10 lines. The Oracle Rownum function gets its value dynamically increasing per every next row and the query is using it in the Lpad length parameter. For example the first row’s Lpad length will be 10 + 1 is 11 and the 10th row’s length will be then 20 characters. Take a look at the output how the left-padded extra size will be filled with *-characters.

 SELECT lpad ('Oracle DBA',10+rownum,'*')
   FROM dual
    CONNECT BY rownum < 11;

It’s important to know that Lpad padding string doesn’t have to be only one character it can be a text or a pattern. The query below is same as the last one with only larger Lpad padding string “*-*-” and the Oracle Lpad will use for first 3 lines only part of the pad string. The pad string will be in it’s full length starting from the 4th row.

 SELECT lpad ('Oracle DBA',10+rownum,'*-*-')
   FROM dual
    CONNECT BY rownum < 11;

The Oracle Lpad truncating example below has Oracle Rownum amount subtracted from the initial amount of 10 character (10-rownum number) and since we do have 10 rows the lowest row Lpad length will become to zero. Take a look what the SQL query returns with decreasing length amount that is smaller than the input string length 10.

 SELECT lpad ('Oracle DBA',10-rownum,'*')
   FROM dual
    CONNECT BY rownum < 11;

The Oracle Lpad can become quite handy to find out white-spaces in the string’s left side or it can be used on reports to align a text on left. There are many more ways to format the output using Oracle Lpad and that makes the function important to know.

See Also:
Oracle Select Oracle Home