This learning page is based on examples. Oracle LPAD function allows formatting an output of a SQL query or a PL/SQL code. The function fills the string from left side with defined characters and by default the padding character is “space”. The Oracle LPAD syntax is following:
LPAD (<string>, <nr_of_length> [,<padding_string>]);
The following LPAD examples are using text “Oracle DBA” and to know the string length we will use Oracle Length function. Take a look at the following example below.
SELECT length('Oracle DBA')
FROM dual;
The output of the last query shows 10 characters. To pad the character we will set Oracle LPAD length to 11 and the output shows the “Oracle DBA” string (with a space in front of it). The LPAD fills by default the free space 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 an asterisk (*). You will see a *-character in front of the “Oracle DBA” string in the output. You may wonder why there is only 1 star – we did declare the length to 11 characters. The Oracle LPAD fills only the free places from left.
SELECT LPAD ('Oracle DBA',11,'*')
FROM dual;
The third LPAD query has length less than 10 characters which is the input string length. Take a look at the query output the “Oracle DBA” has become to “Oracle” and there are no star characters on the left side. Oracle LPAD will truncate your input string if it doesn’t fit into the length size and there are no stars because no free space has available. The input string doesn’t fit into 6 characters.
SELECT LPAD ('Oracle DBA',6,'*')
FROM dual;
The example below shows how to format the SQL query output using Oracle LPAD function. The Select statement is done 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 it set in the query to use it as the LPAD length parameter. For example on the first row’s LPAD length will be 10 + 1 or 11 and on the 10th row’s length are 20 characters. Take a look at the output how the left-padded free space will be filled with *-characters.
SELECT LPAD ('Oracle DBA',10+rownum,'*')
FROM dual
CONNECT BY rownum < 11;
It’s important to know that pad string doesn’t have to be only 1 character it can be a text or a pattern. The query below is same as the last one with only larger LPAD string “*-*-” and Oracle LPAD will use for first 3 lines only part of it. The pad string will be in its 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 amount of 10 (10-rownum) and since we do have 10 rows the lowest row LPAD length will become zero. The SQL query returns text with decreasing length amount and that is smaller than the input string own length (10).
SELECT LPAD ('Oracle DBA',10-rownum,'*')
FROM dual
CONNECT BY rownum < 11;
Oracle LPAD can become quite handy to find out white-spaces in the string on the left side or it can be used on reports to align text on left. There are many more ways to format the output using Oracle LPAD and that makes it quite important to know.
See Also:
Oracle Select Home