SUBSTR, SUBSTRB, SUBSTR4

Returns a string that represents a substring of a source string. The returned substring is of a specified number of characters, beginning from a designated starting point, relative to either the beginning or end of the string.

SQL syntax

{SUBSTR | SUBSTRB | SUBSTR4}=(Source, m, n)

Parameters

SUBSTR has the parameters:

Parameter	Description
Source	The string for which this function returns a substring. Value can be any supported character data types including CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB data types. Both TimesTen and Oracle Database data types are supported. If Source is a CHAR string, the result is a CHAR or VARCHAR2 string. If Source is a NCHAR string, the result is a NVARCHAR2 string. If Source is a LOB, the result is the same LOB data type.
m	The position at which to begin the substring. If m is positive, the first character of the returned string is m characters from the beginning of the string specified in char. Otherwise it is m characters from the end of the string. If ABS(m) is bigger than the length of the character string, a null value is returned.
n	The number of characters to be included in the substring. If n is omitted, all characters to the end of the string specified in char are returned. If n is less than 1 or if char, m or n is NULL, NULL is returned.

Description

SUBSTR calculates lengths using characters as defined by character set. SUBSTRB uses bytes instead of characters. SUBSTR4 uses UCS4 code points.

Examples

In the first five rows of employees, select the first three characters of last_name:

SELECT FIRST 5 SUBSTR(last_name,1,3) FROM employees;
< Kin >
< Koc >
< De  >
< Hun >
< Ern >
5 rows found.

In the first five rows of employees, select the last five characters of last_name:

SELECT FIRST 5 SUBSTR(last_name,-5,5) FROM employees;
< <NULL> >
< chhar >
<  Haan >
< unold >
< Ernst >
5 rows found.