Substrings

In any context in which you reference a character item, you can reference a substring, which is a sequential subset of the characters in that item. If an item value is ABCD, you can reference (for example) BC, which is the second and third character.

The syntax of a substring reference is as follows.

itemReference

An item of type CHAR, MBCHAR, DBCHAR. HEX, or UNICODE, but not a literal. The item may be a system variable or an array element.

fromIndex

The first character of interest in the item, where 1 represents the first character in the character item, 2 represents the second, and so on. You can use an integer literal or an item of type BIGINT, INT, SMALLINT or a BIN-type equivalent.

The value of fromIndex represents a byte position unless itemReference refers to an item of type DBCHAR or UNICODE, in which case the value represents a double-byte character position.

Count from the leftmost character, even if you are working with a bidirectional language such as Arabic or Hebrew.

toIndex

The last character of interest in the item, where 1 represents the first character in the character item, 2 represents the second, and so on. You can use an integer literal or an item of type BIGINT, INT, SMALLINT or a BIN-type equivalent.

The value of toIndex represents a byte position unless itemReference refers to an item of type DBCHAR or UNICODE, in which case the value represents a double-byte character position.

Count from the leftmost character, even if you are working with a bidirectional language such as Arabic or Hebrew.

Related concepts
References to variables and constants

Related tasks
Syntax diagram

Related reference