NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
S-84
Examples—SUBSTRING Function
•
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 then the result is null.
Examples—SUBSTRING Function
•
The following example returns “John”:
SUBSTRING("Robert John Smith" FROM 8 FOR 4)
•
The following example returns “John Smith”:
SUBSTRING("Robert John Smith" FROM 8)
•
The following example returns “Robert John Smith”:
SUBSTRING("Robert John Smith" FROM 1 FOR 17)
•
The following example returns “John Smith”:
SUBSTRING ("Robert John Smith" FROM 8 FOR 15)
•
The following example returns “”:
SUBSTRING ("Robert John Smith" FROM -5 FOR 2)
•
The following example returns “Ro”:
SUBSTRING ("Robert John Smith" FROM -2 FOR 5)
•
The following example produces an empty string, “”, which is different than a null
value for the result:
SUBSTRING("Robert John Smith" FROM 8 FOR 0)