substr截取字符串

Purpose

The SUBSTR functions return a portion ofchar, beginning at characterposition,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. Ifsubstring_length is less than 1, then Oracle returns null.

char can be any of the datatypesCHAR, VARCHAR2,NCHAR, NVARCHAR2,CLOB, or NCLOB. Bothposition andsubstring_length must 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 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---------CDEFSELECT 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