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

   <source lang="sql">
 

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>

 </source>
   
  

Padding department with < and >

   <source lang="sql">

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.

 </source>
   
  

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

   <source lang="sql">
 

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

 </source>
   
  

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

   <source lang="sql">

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.............

 </source>
   
  

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

   <source lang="sql">
 

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

 2  from dual
 3  /

RPAD(

---

1 row selected. SQL> SQL> --

 </source>
   
  

Use rpad to add extra space

   <source lang="sql">
 

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> --

 </source>