Example SELECT output using SUBSTR(). : SUBSTR « Character String Functions « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Character String Functions
  3. SUBSTR
SQL>
SQL> select substr(owner,1,10) owner,
  2      substr(object_name,1,10) object_name,
  3      substr(object_type, 1,10) object_type,
  4      to_char(last_ddl_time,'dd-mon-yyyy hh24:mi') last_ddl_time
  5  from dba_objects
  6  where owner='SYSTEM' and rownum < 50;

OWNER      OBJECT_NAM OBJECT_TYP LAST_DDL_TIME
---------- ---------- ---------- -----------------
SYSTEM     SYSCATALOG SYNONYM    07-feb-2006 22:12
SYSTEM     CATALOG    SYNONYM    07-feb-2006 22:12
SYSTEM     TAB        SYNONYM    07-feb-2006 22:12
SYSTEM     COL        SYNONYM    07-feb-2006 22:12
SYSTEM     TABQUOTAS  SYNONYM    07-feb-2006 22:12
SYSTEM     SYSFILES   SYNONYM    07-feb-2006 22:12
SYSTEM     PUBLICSYN  SYNONYM    07-feb-2006 22:12
SYSTEM     MVIEW$_ADV TABLE      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV INDEX      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV INDEX      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV TABLE      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV INDEX      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV TABLE      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV INDEX      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV TABLE      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV INDEX      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV TABLE      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV INDEX      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV TABLE      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV INDEX      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV TABLE      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV INDEX      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV TABLE      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV TABLE      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV INDEX      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV TABLE      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV INDEX      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV TABLE      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV INDEX      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV TABLE      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV INDEX      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV TABLE      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV INDEX      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV TABLE      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV INDEX      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV TABLE      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV INDEX      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV TABLE      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV INDEX      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV TABLE      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV TABLE      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV INDEX      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV TABLE      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV INDEX      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV TABLE      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV INDEX      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV TABLE      07-feb-2006 22:13
SYSTEM     MVIEW$_ADV SEQUENCE   07-feb-2006 22:13
SYSTEM     MVIEW$_ADV SEQUENCE   07-feb-2006 22:13

49 rows selected.

SQL>
SQL>
SQL>








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