SUBSTR function
Derby Reference Manual
140
Examples
-- throws an exception if any row stores a negative number:
VALUES SQRT(3421E+09)
-- returns the square root of an INTEGER after casting it as a
-- floating point data type:
SELECT SQRT(myDoubleColumn) FROM MyTable
VALUES SQRT (CAST(25 AS FLOAT));
SUBSTR function
The SUBSTR function acts on a character string expression or a bit string expression.
The type of the result is a
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
'.
SUM function
SUM is an aggregate function that evaluates the sum of the expression over a set of rows
(see
). SUM is allowed only on expressions that evaluate to
numeric data types.
