SUBSTR function

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 ] )

The parameter startPosition and the optional parameter 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.

The parameter 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, the startPosition and lengthOfString parameters refer to characters. For bit expressions, the startPosition and lengthOfString parameters refer to bits.

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

If the 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. The SUBSTR function returns an error if you specify a negative number for the parameter lengthOfString.

Examples

To return a substring of the word hello, starting at the second character and continuing until the end of the word, use the following clause:

VALUES SUBSTR('hello', 2)

The result is 'ello'.

To return a substring of the word hello, starting at the first character and continuing for two characters, use the following clause:

VALUES SUBSTR('hello',1,2)

The result is 'he'.