Returns a portion of a string. SUBSTR counts all characters, including blanks, starting at the beginning of the string.
Syntax
SUBSTR( string, start [,length] )
Argument
Required/
Optional
Description
string
Required
Must be a character string. Passes the strings you want to search. You can enter any valid expression. If you pass a numeric value, the function converts it to a character string.
start
Required
Must be an integer. The position in the string where you want to start counting. You can enter any valid expression. If the start position is a positive number, SUBSTR locates the start position by counting from the beginning of the string. If the start position is a negative number, SUBSTR locates the start position by counting from the end of the string. If the start position is 0, SUBSTR searches from the first character in the string.
length
Optional
Must be an integer greater than 0. The number of characters you want SUBSTR to return. You can enter any valid expression. If you omit the length argument, SUBSTR returns all of the characters from the start position to the end of the string. If you pass a negative integer or 0, the function returns an empty string. If you pass a decimal, the function rounds it to the nearest integer value.
Return Value
String.
Empty string if you pass a negative or 0 length value.
NULL if a value passed to the function is NULL.
Example
The following expressions return the area code for each row in the PHONE column:
SUBSTR( PHONE, 0, 3 )
PHONE
RETURN VALUE
809-555-0269
809
357-687-6708
357
NULL
NULL
SUBSTR( PHONE, 1, 3 )
PHONE
RETURN VALUE
809-555-3915
809
357-687-6708
357
NULL
NULL
The following expressions return the phone number without the area code for each row in the PHONE column:
SUBSTR( PHONE, 5, 8 )
PHONE
RETURN VALUE
808-555-0269
555-0269
809-555-3915
555-3915
357-687-6708
687-6708
NULL
NULL
You can also pass a negative start value to return the phone number for each row in the PHONE column. The expression still reads the source string from left to right when returning the result of the length argument:
SUBSTR( PHONE, -8, 3 )
PHONE
RETURN VALUE
808-555-0269
555
809-555-3915
555
357-687-6708
687
NULL
NULL
You can nest INSTR in the start or length argument to search for a specific string and return its position.
The following expression evaluates a string, starting from the end of the string. The expression finds the last (right-most) space in the string and then returns all characters preceding it:
When the length argument is longer than the string, SUBSTR returns all the characters from the start position to the end of the string. Consider the following example:
SUBSTR('abcd', 2, 8)
The return value is ‘bcd’. Compare this result to the following example: