SUBSTRING/SUBSTR extracts n2 characters or bytes from string_expression starting at position n1.
To get the number of characters or bytes in the resulting string, use the BYTE function for byte strings and the CHARACTER_LENGTH function for character strings.
If either of the following conditions are true, SUBSTRING/SUBSTR returns a zero length string:
- (n1 > string_length) AND (0 ≤ n2)
- (n1 < 1) AND (0 ≤ n2) AND ((n2 + n1 - 1) ≤ 0)
| IF n2 is … | THEN … | ||||||
|---|---|---|---|---|---|---|---|
| specified | |||||||
| IF … | THEN … | ||||||
| n2 < 0 | SUBSTRING/SUBSTR returns an error. | ||||||
| 0 ≤ n2 and n1 > string_length |
SUBSTRING/SUBSTR returns a string containing zero characters. | ||||||
| 0 ≤ n2 and n1 < 1 |
SUBSTRING/SUBSTR sets n4 = n2 + n1 - 1 and sets n3 = 1. | ||||||
| IF … | THEN SUBSTRING/ SUBSTR returns … | ||||||
| n4 ≤ 0 | a string containing zero characters. | ||||||
| n4 > string_length | the source string. | ||||||
| 0 < n4 <= string_length | a string that starts at n3 and extends for n4 characters. | ||||||
| 0 < n2 AND 1 ≤ n1 ≤ string_length |
|||||||
| IF … | THEN SUBSTRING/ SUBSTR returns a string … | ||||||
| (n1 + n2 - 1) > string_length | that starts at n1 and ends with the last character of the source string. | ||||||
| 0 < (n1 + n2 - 1) ≤ string_length | that starts at n1 and extends for n2 characters. | ||||||
| not specified | |||||||
| IF … | THEN SUBSTRING/SUBSTR returns … | ||||||
| n1 < 1 | the source string. If the source string is a CHAR type, trailing pad characters are trimmed. |
||||||
| n1 > string_length | a string containing zero characters. | ||||||
| 1 ≤ n1 ≤ string_length | a string that starts at n1 and ends with the last character of the source string. If the source string is a CHAR type, trailing pad characters are trimmed. |
||||||