Neoview SQL Reference Manual (R2.4 SP2)

SUBSTRING/SUBSTR Function
The SUBSTRING function extracts a substring out of a given character expression. It returns a
character string of data type VARCHAR, with a maximum length equal to the smaller of these
two:
• The fixed length of the input string (for CHAR-type strings) or the maximum variable length
(for VARCHAR-type strings)
• The value of the length argument (when a constant is specified) or 32708 (when a non-constant
is specified)
SUBSTR is equivalent to SUBSTRING.
SUBSTRING (character-expr FROM start-position [FOR length])
or:
SUBSTRING (character-expr,start-position[,length])
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” (page 243).
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. Keep in mind that
every character, including multibyte characters, counts as one character. 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. The length field is optional, so 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.
The length field is optional. If you do not specify it, all characters starting at start-position
and continuing until the end of the character-expr are returned.
Alternative Forms
• The SUBSTRING function treats SUBSTRING( string FOR int ) equivalent to SUBSTRING(
string FROM 1 FOR int ). The Neoview database software already supports the ANSI
standard form as:
SUBSTRING(string FROM int [ FOR int ])
• The SUBSTRING function treats SUBSTRING (string, Fromint) equivalent to
SUBSTRING(string FROM Fromint). The Neoview database software already supports
SUBSTRING (string, Fromint, Forint) as equivalent to the ANSI standard form:
SUBSTRING(string FROM Fromint FOR Forint)
Considerations for SUBSTRING/SUBSTR
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.
SUBSTRING/SUBSTR Function 459