Purpose
SUBSTR() extracts a substring from a string. A multi-byte character, such as a Chinese character or a full-width character, counts as one character.
Syntax
SUBSTR(char1,n1[,n2])
Parameters
| Parameter | Description |
|---|---|
| char1 | The string from which the substring is to be extracted. It can be of the CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB data type. |
| n1 | The starting position for extraction. It is of the NUMBER data type. Note
|
| n2 | The length of the substring to extract. This optional parameter is of the NUMBER data type. Note
|
Return type
The return type is the same as the data type of char1.
Examples
The following example extracts, from the string OceanBase, 5 characters starting from the 1st character and 2.1 characters starting from the -3rd character.
obclient> SELECT SUBSTR('OceanBase',1,5) "SUBSTR1",SUBSTR('OceanBase',-3,2.1) "SUBSTR2" FROM DUAL;
+---------+---------+
| SUBSTR1 | SUBSTR2 |
+---------+---------+
| Ocean | as |
+---------+---------+
1 row in set