SUBSTR
Returns a portion of a string. SUBSTR counts all characters, including blanks, starting at the beginning of the string.
Syntax
SUBSTR( string, start [,length] )
The following table describes the arguments for this command:
Argument | Required/ Optional | Description |
|---|
string | Required | Must be a character string. Passes the strings you want to search. You can enter any valid transformation 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 transformation 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 transformation 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.
Examples
The following expressions return the area code for each row in the Phone port:
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 port:
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 port. 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:
SUBSTR( CUST_NAME,1,INSTR( CUST_NAME,' ' ,-1,1 ) - 1 )
CUST_NAME | RETURN VALUE |
|---|
PATRICIA JONES | PATRICIA |
MARY ELLEN SHAH | MARY ELLEN |
The following expression removes the character '#' from a string:
SUBSTR( CUST_ID, 1, INSTR(CUST_ID, '#')-1 ) || SUBSTR( CUST_ID, INSTR(CUST_ID, '#')+1 )
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:
SUBSTR('abcd', -2, 8)
The return value is ‘cd’.