SUBSTR

The SUBSTR function acts on a character string expression or a bit string expression. The type of the result is a VARCHAR in the first case and VARCHAR FOR BIT DATA in the second case. The length of the result is the maximum length of the source type.

Syntax

SUBSTR({ CharacterExpression },
   StartPosition [, LengthOfString ] )

startPosition and the optional lengthOfString are both integer expressions. (The first character or bit has a startPosition of 1; if you specify 0, Derby assumes that you mean 1.)

A characterExpression is a CHAR, VARCHAR, or LONG VARCHAR data type or any built-in type that is implicitly converted to a string (except a bit expression).

For character expressions, both startPosition and lengthOfString refer to characters. For bit expressions, both startPosition and lengthOfString refer to bits.

SUBSTR returns NULL if lengthOfString is specified and it is less than zero.

If startPosition is positive, it refers to position from the start of the source expression (counting the first character as 1). If startPosition is negative, it is the position from the end of the source.

If lengthOfString is not specified, SUBSTR returns the substring of the expression from the startPosition to the end of the source expression. If lengthOfString is specified, SUBSTR returns a VARCHAR or VARBIT of length lengthOfString starting at the startPosition.