Oracle PL/SQL/Char Functions/RPAD

LPAD() and RPAD(): pad the input parameter of character data types with blanks (or another character) from the left or right

  
Syntax:LPAD(<character string>, <length_of_resulting_string> [,<padding_string>])
RPAD(<character string>, <length_of_resulting_string>  [,<padding_string>])

SQL> SELECT
  2     LPAD("ABC",6,"***") result
  3  FROM dual;
RESULT
------
***ABC
SQL>

Padding department with < and >

 
SQL> create table departments
  2  ( deptno NUMBER(2)     constraint D_PK
  3                         primary key
  4  , dname  VARCHAR2(10)
  5  , location VARCHAR2(8)
  6  , mgr    NUMBER(4)
  7  ) ;
Table created.
SQL>
SQL> insert into departments values (10,"ACCOUNTING","NEW YORK",7);
1 row created.
SQL> insert into departments values (20,"TRAINING",  "DALLAS",  4);
1 row created.
SQL> insert into departments values (30,"SALES",     "CHICAGO", 6);
1 row created.
SQL> insert into departments values (40,"HR",        "BOSTON",  9);
1 row created.
SQL>
SQL> select dname
  2  ,      lpad(dname,9,">")
  3  ,      rpad(dname,6,"<")
  4  from   departments;
DNAME      LPAD(DNAM RPAD(D
---------- --------- ------
ACCOUNTING ACCOUNTIN ACCOUN
TRAINING   >TRAINING TRAINI
SALES      >>>>SALES SALES<
HR         >>>>>>>HR HR<<<<
SQL>
SQL>
SQL> drop table departments;
Table dropped.

RPAD() function"s behavior is identical to that of LPAD(), the only difference being the padding side.

  
SQL>
SQL> SELECT
  2     RPAD("ABC",6,"***") result1,
  3     LPAD("ABC",4,"***") result2,
  4     RPAD("ABC",2,"***") result3
  5  FROM dual;
RESULT RESU RE
------ ---- --
ABC*** *ABC AB

Rpad: makes a string a certain length by adding (padding) a specified set of characters to the right

 
SQL> -- Rpad: makes a string a certain length by adding (padding) a specified set of characters to the right .
SQL>
SQL> -- RPAD(string, length_to_make_string,  what_to_add_to_right_of_string)
SQL>
SQL> SELECT RPAD("Letters", 20, ".") FROM dual;
RPAD("LETTERS",20,".
--------------------
Letters.............

select rpad( "*", 5, "*" )

  
SQL>
SQL>
SQL> select rpad( "*", 5, "*" )
  2  from dual
  3  /
RPAD(
-----
*****
1 row selected.
SQL>
SQL> --

Use rpad to add extra space

  
SQL>
SQL> CREATE TABLE EMP
  2  (EMPNO NUMBER(4) NOT NULL,
  3   ENAME VARCHAR2(10),
  4   JOB VARCHAR2(9),
  5   MGR NUMBER(4),
  6   HIREDATE DATE,
  7   SAL NUMBER(7, 2),
  8   COMM NUMBER(7, 2),
  9   DEPTNO NUMBER(2)
 10  );
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH",  "CLERK",     7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"),  800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN",  "SALESMAN",  7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600,  300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD",   "SALESMAN",  7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250,  500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES",  "MANAGER",   7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"),  2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN",  7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE",  "MANAGER",   7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"),  2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK",  "MANAGER",   7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"),  2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT",  "ANALYST",   7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING",   "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN",  7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"),  1500,    0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS",  "CLERK",     7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES",  "CLERK",     7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"),   950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD",   "ANALYST",   7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"),  3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK",     7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL>  select rpad( ename, 10, " " ) || "(" || job || ")" "Responsibilities"
  2      from emp
  3     order by ename
  4    /
Responsibilities
---------------------
ADAMS     (CLERK)
ALLEN     (SALESMAN)
BLAKE     (MANAGER)
CLARK     (MANAGER)
FORD      (ANALYST)
JAMES     (CLERK)
JONES     (MANAGER)
KING      (PRESIDENT)
MARTIN    (SALESMAN)
MILLER    (CLERK)
SCOTT     (ANALYST)
SMITH     (CLERK)
TURNER    (SALESMAN)
WARD      (SALESMAN)
14 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
SQL> --