substr(,) and substr(,,) : SUBSTR « Character String Functions « Oracle PL/SQL Tutorial

SQL>
SQL> create table departments
  2  ( deptno NUMBER(2)
  3  , dname  VARCHAR2(10)
  4  , location VARCHAR2(20)
  5  , mgr    NUMBER(4)
  6  ) ;

Table created.

SQL>
SQL> insert into departments values (10,'ACCOUNTING','NEW YORK' ,  2);

1 row created.

SQL> insert into departments values (20,'TRAINING',  'VANCOUVER',  3);

1 row created.

SQL> insert into departments values (30,'SALES',     'CHICAGO',    4);

1 row created.

SQL> insert into departments values (40,'HR',        'BOSTON',     5);

1 row created.

SQL>
SQL>
SQL>
SQL> select dname
  2  ,      substr(dname,4)      as substr1
  3  ,      substr(dname,4,3)    as substr2
  4  from   departments;

DNAME      SUBSTR1 SUBSTR2
---------- ------- -------
ACCOUNTING OUNTING OUN
TRAINING   INING   INI
SALES      ES      ES
HR

SQL>
SQL> drop table departments;

Table dropped.

11.18.SUBSTR
	11.18.1.	The SUBSTR Function
	11.18.2.	substr(,) and substr(,,)
	11.18.3.	SUBSTR(x, start [, length]) returns a substring of x that begins at the position specified by start.
	11.18.4.	Substr retrieves a portion of the string
	11.18.5.	Retrieval begins at position 5 and again goes for 12 characters
	11.18.6.	A retrieval with no third argument: it starts at begin and retrieves the rest of the string
	11.18.7.	If begin is negative, then retrieval occurs from the right-hand side of original string
	11.18.8.	SUBSTR and INSTR used together
	11.18.9.	Combine SUBSTR and INSTR to deal with a table column
	11.18.10.	Combine DECODE and SUBSTR together
	11.18.11.	Example SELECT output using SUBSTR().
	11.18.12.	substr birthday
	11.18.13.	Compare substring in where statement
	11.18.14.	Manipulating Strings: SUBSTR and concatenation
	11.18.15.	substr(date value,8)+16