RPAD and LPAD functions in Oracle

RPAD:

1. Allows you to pad set of characters on the right side of a column.
2. The character set can be spaces, periods, commas, numbers,caret signs, exclamation signs or any other valid characters.

   Syntax

   RPAD(string, length,[,’set’])

   Where 

   String is : any char or varchar2 column from a database or literal string.
   Length: Total No of characters required to do the padding

   Set: 
   It is optional part, here you can specify which character you want to pad  with. For example you might want to pad * or say $ or dot (.) on the right side of the string.

        If you omit the set parameter then the RPAD function will pad the     
       string with empty spaces.
 

          Let’s look at simple example

–creating a test table

CREATE TABLE testbook ( bookname varchar(10), totalpages number(10));

 

–inserting test data into the table

INSERT INTO testbook VALUES (‘Book 1′,15);

INSERT INTO testbook VALUES (‘Book 2′,1089);

INSERT INTO testbook VALUES (‘MAGIC BOOK’,565);

 

–using the RPAD Function

SELECT RPAD(bookname,45,‘.’), totalpages from testbook;

 

 

LPAD: It is the opposite of RPAD.

Execute the below statement

 

SELECT LPAD(bookname,45,‘.’), totalpages from testbook;

 

Result

 

Happy Learning..!!!