Oracle SQL - Using the LPAD and RPAD Functions

  1. HOME
  2. Oracle SQL
  3. Select
  4. Text Functions

Introduction

The following code demonstrates using the LPAD and RPAD functions.

They can not only enlarge strings, as their names suggest, but sometimes they also shorten strings.

Demo

SQL>
SQL>-- www . j a  v a  2s  .co  m
SQL> drop table departments;

Table dropped.

SQL>
SQL> create table departments(
  2  deptno    NUMBER(2)     primary key,
  3  dname     VARCHAR2(10)  not null unique check (dname = upper(dname)),
  4  location  VARCHAR2(8)   not null        check (location = upper(location)),
  5  mgr       NUMBER(4)) ;
SQL>
SQL> insert into departments values (10,'ACCOUNTING','NEW YORK',7007);
SQL> insert into departments values (20,'TRAINING',  'DALLAS',  7004);
SQL> insert into departments values (30,'SALES',     'CHICAGO', 7006);
SQL> insert into departments values (40,'HR',        'BOSTON',  7009);
SQL>
SQL> select dname
  2  ,      lpad(dname,9,'>')
  3  ,      rpad(dname,6,'<')
  4  from   departments;

DNAME      | LPAD(DNAME,9,'>')                    | RPAD(DNAME,6,'<')
---------- | ------------------------------------ | ------------------------
ACCOUNTING | ACCOUNTIN                            | ACCOUN
TRAINING   | >TRAINING                            | TRAINI
SALES      | >>>>SALES                            | SALES<
HR         | >>>>>>>HR                            | HR<<<<

SQL>

Related Topic