Oracle PL/SQL - SUBSTR and INSTR

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

Introduction

SUBSTR retrieves part of existing string: 

v_tx:= substr(string, start position[,number of chars]);

The start position could be either a positive or negative integer.

This would start counting the position from the beginning or from the end of the string:

Demo

SQL>
SQL> declare-- from   w  w  w  . j a v a2s.  com
  2      v1_tx VARCHAR2(5):='ABCDE';
  3      v2_tx VARCHAR2(5);
  4  begin
  5      v2_tx:=substr(v1_tx,2);
  6      DBMS_OUTPUT.put_line(v2_tx);
  7      v2_tx:=substr(v1_tx,-2);
  8      DBMS_OUTPUT.put_line(v2_tx);
  9  end;
 10  /
BCDE
DE

PL/SQL procedure successfully completed.
SQL>

You can omit the third parameter: requested number of characters.

In that case, Oracle returns everything from the point you specified to the end of the string.

If your starting point is more than the total number of characters in the string, Oracle returns NULL.

Demo

SQL>
SQL> declare-- from  w ww  . j a  v a  2s  .  c  om
  2      v1_tx VARCHAR2(5):='ABCDE';
  3      v2_tx VARCHAR2(5);
  4  begin
  5      v2_tx:=substr(v1_tx,2,2);
  6      DBMS_OUTPUT.put_line(v2_tx);
  7      v2_tx:=substr(v1_tx,2,7);
  8      DBMS_OUTPUT.put_line(v2_tx);
  9  end;
 10  /
BC
BCDE

PL/SQL procedure successfully completed.
SQL>

Related Topic