Substr()函数

Substr Function

Oracle/PLSQL: Substr Function

---

In Oracle/PLSQL, the substr functions allows you to extract a substring from a string.

The syntax for the substr function is:

substr( string, start_position, [ length ] )

string is the source string.

start_position is the position for extraction. The first position in the string is always 1.

length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.

Note:

If start_position is 0, then substr treats start_position as 1 (ie: the first position in the string).

If start_position is a positive number, then substr starts from the beginning of the string.

If start_position is a negative number, then substr starts from the end of the string and counts backwards.

If length is a negative number, then substr will return a NULL value.

For example:

substr('This is a test', 6, 2)	would return 'is'
substr('This is a test', 6)	would return 'is a test'
substr('TechOnTheNet', 1, 4)	would return 'Tech'
substr('TechOnTheNet', -3, 3)	would return 'Net'
substr('TechOnTheNet', -6, 3)	would return 'The'
substr('TechOnTheNet', -8, 2)	would return 'On'

PL/SQL BOOCK:

variable g_char varchar2(30);

variable g_num number;

declare

v_char varchar2(30);

v_num number;

begin

   v_char := '42 is answer';

   :g_char := v_char;

   :g_num := substr(V_CHAR,1,2);

end;

/

print g_char;

print g_num;