SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-145
SUBSTRING Function
SUBSTRING Function
The SUBSTRING function extracts a substring out of a given character expression. It
returns a character string of data type VARCHAR, with maximum length equal to the
fixed length or maximum variable length of the character expression.
character-expr
specifies the source string from which to extract the substring. The source string is
an SQL character value expression. The operand is the result of evaluating
character-expr. See Character Value Expressions on page 6-37.
start-position
specifies the starting position start-position within character-expr at
which to start extracting the substring. start-position must be a value with an
exact numeric data type and a scale of zero.
length
specifies the number of characters to extract from character-expr.length is
the length of the extracted substring and must be a value greater than or equal to
zero of exact numeric data type and with a scale of zero.
If you are using the FROM keyword, the length field is optional, therefore, if you
do not specify the substring length, all characters starting at start-position
and continuing until the end of the character expression are returned. If you are not
using the FROM and FOR keywords, the length field is required.
Considerations for SUBSTRING
Requirements for the Expression, Length, and Start Position
•
The data types of the substring length and the start position must be numeric with
a scale of zero. Otherwise, an error is returned.
•
If the sum of the start position and the substring length is greater than the length of
the character expression, the substring from the start position to the end of the
string is returned.
•
If the start position is greater than the length of the character expression, an empty
string ('') is returned.
SUBSTRING (character-expr FROM start-position [FOR length])
or:
SUBSTRING (character-expr,start-position,length)