SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
S-85
Considerations—SUBSTRING Function
substring-length
specifies the number of characters to extract from character-string.
substring-length must be a value of exact numeric data type with a scale of
zero; otherwise, SQL returns an error. substring-length must be greater than
or equal to zero.
Considerations—SUBSTRING Function

The data types of substring-length and start-position must be numeric;
otherwise, SQL returns an error.

If the sum of start-position and substring-length is greater than the
length of the character string, SQL returns the substring from start-position to
the end of the string.

If the sum of start-position and substring-length is less than zero, SQL
returns an empty string (“”).

If start-position is greater than the length of the character string, SQL returns
an empty string (“”).

If you do not specify substring-length, SQL returns all characters starting at
start-position and continuing until the end of character-string.

The resulting substring is always of type VARCHAR, with the same collating
sequence and character set as the source character string. If the source character
string is an upshifted CHAR or VARCHAR string, the result is an upshifted
VARCHAR type.

The resulting collating sequence and character set are the same as that of the
string operand, character-string.

If character-string, start-position, or substring-length is a null
value, the result is null.
Examples—SUBSTRING Function

This example returns “John”:
SUBSTRING("Robert John Smith" FROM 8 FOR 4)

This example returns “John Smith”:
SUBSTRING("Robert John Smith" FROM 8)

This example returns “Robert John Smith”:
SUBSTRING("Robert John Smith" FROM 1 FOR 17)

This example returns “John Smith”:
SUBSTRING ("Robert John Smith" FROM 8 FOR 15)