SUBSTR

SUBSTR

Syntax

substr::=

Description of the illustration substr.gif

Purpose

The SUBSTR functions return a portion ofchar, beginning at character position, substring_length characters long.SUBSTR calculates lengths using characters as defined by the input character set.SUBSTRB uses bytes instead of characters. SUBSTRC uses Unicode complete characters.SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points.

• If position is 0, then it is treated as 1.

• If position is positive, then Oracle Database counts from the beginning ofchar to find the first character.

• If position is negative, then Oracle counts backward from the end ofchar.

• If substring_length is omitted, then Oracle returns all characters to the end ofchar. If substring_length is less than 1, then Oracle returns null.

char can be any of the datatypesCHAR, VARCHAR2, NCHAR, NVARCHAR2,CLOB, or NCLOB. Both position andsubstring_length must be of datatypeNUMBER, or any datatype that can be implicitly converted to NUMBER, and must resolve to an integer. The return value is the same datatype aschar. Floating-point numbers passed as arguments toSUBSTR are automatically converted to integers.

See Also:

Oracle Database Globalization Support Guide for more information aboutSUBSTR functions and length semantics in different locales

Examples

The following example returns several specified substrings of "ABCDEFG":

SELECT SUBSTR('ABCDEFG',3,4) "Substring"
     FROM DUAL;
 
Substring
---------
CDEF

SELECT SUBSTR('ABCDEFG',-5,4) "Substring"
     FROM DUAL;

Substring
---------
CDEF

Assume a double-byte database character set:

SELECT SUBSTRB('ABCDEFG',5,4.2) "Substring with bytes"
     FROM DUAL;

Substring with bytes
--------------------
CD

From:http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions162.htm#SQLRF06114