SUBSTR : CHAR VARCHAR2 Functions « PL SQL Data Types « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Data Types
3. CHAR VARCHAR2 Functions

SUBSTR retrieves part of existing string:

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

1. The start position could be either a positive or negative integer.
2. Positive value would let SUBSTR start counting the position from the beginning.
3. Negative value would let SUBSTR start counting the position from the end of the string.
4. If your starting point is more than the total number of characters in the string, Oracle returns NULL.

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

The number of characters requested from the string might not always be the length of the resulting string.

You might request more characters than the string has.

Oracle just returns everything up to the end of the string.

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