Oracle SUBSTR函数

SUBSTR

Syntax

substr::=

Description of the illustration substr.gif

Purpose

TheSUBSTRfunctions return a portion ofchar, beginning at characterposition,substring_lengthcharacters long.SUBSTRcalculates lengths using characters as defined by the input character set.SUBSTRBuses bytes instead of characters.SUBSTRCuses Unicode complete characters.SUBSTR2uses UCS2 code points.SUBSTR4uses UCS4 code points.

• Ifpositionis 0, then it is treated as 1.

• Ifpositionis positive, then Oracle Database counts from the beginning ofcharto find the first character.

• Ifpositionis negative, then Oracle counts backward from the end ofchar.

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

charcan be any of the datatypesCHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB, orNCLOB. Bothpositionandsubstring_lengthmust be of datatypeNUMBER, or any datatype that can be implicitly converted toNUMBER, and must resolve to an integer. The return value is the same datatype aschar. Floating-point numbers passed as arguments toSUBSTRare automatically converted to integers.

See Also:

Oracle Database Globalization Support Guidefor more information about SUBSTRfunctions 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