Oracle PL/SQL - PAD and TRIM

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. CHAR and VARCHAR2 Variables Size

Introduction

A number of functions allow you to either add (PAD) or remove (TRIM) characters to an existing string.

LPAD/LTRIM do it from the left side of the string.

RPAD/RTRIM from the right side.

TRIM function allows you to select the trimming mode:

  • left side - leading
  • right side - trailing
  • both

Syntax 

v_tx:= lpad(string,length,extra string);
v_tx:= ltrim(string [,character]);
v_tx:= trim(LEADING|TRAILING|BOTH character from string);

Demo

SQL>
SQL> declare-- from  ww w.j a  v a  2 s .c  o  m
  2      v1_tx VARCHAR2(20):='Hello!';
  3      v2_tx VARCHAR2(20);
  4      v3_tx VARCHAR2(20);
  5  begin
  6      v2_tx:= rpad(lpad(v1_tx,10,'*'),15,'*');
  7      DBMS_OUTPUT.put_line(v2_tx);
  8      v3_tx:= trim (both '*' from v2_tx);
  9      DBMS_OUTPUT.put_line(v3_tx);
 10      v3_tx:= trim (leading '*' from v2_tx);
 11      DBMS_OUTPUT.put_line(v3_tx);
 12      v3_tx:= trim (trailing '*' from v2_tx);
 13      DBMS_OUTPUT.put_line(v3_tx);
 14  end;
 15  /
****Hello!*****
Hello!
Hello!*****
****Hello!

PL/SQL procedure successfully completed.
SQL>

Related Topic