The SUBSTR function extracts a substring. A multibyte character, such as a Chinese character and a full-width character, is calculated as one character.
Syntax
SUBSTR(c1,n1[,n2])
Parameters
| Parameter | Description |
|---|---|
| c1 | The string to be truncated. The string type can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB. |
| n1 | The start position for truncating the string. If n1 is equal to 0 or 1, the string is truncated from the first character. |
| n2 | The length of the truncated string. If you do not specify n2, the truncated string starts from the n1th character to the end. |
Return type
The data that has the same type as c1 is returned. If n2 is 0, it is considered as 1. If n2 is positive, ApsaraDB for OceanBase searches for the first character from the beginning of c1. If n2 is negative, ApsaraDB for OceanBase counts backward c1 from the end of c1. If n3 is omitted, ApsaraDB for OceanBase returns all the characters of c1. If n3 is less than 1, ApsaraDB for OceanBase returns NULL.
Examples
Execute the following statement:
SELECT SUBSTR('1390000****',3,8) test FROM DUAL;
The following query result is returned:
+----------+
| TEST |
+----------+
| 08888888 |
+----------+