Syntax
SUBSTRING(String,Start,Length)
Description
The SUBSTRING SQL scalar function is used to obtain a substring of a string.
The result is a character string.
If any argument of the SUBSTRING function can be null, the result can be null; if any argument is null, the result is the null value.
string |
Denotes an argument that specifies the string from which the result is derived. A substring of string is zero or more contiguous bytes of string. |
start |
Denotes an argument that specifies the position of the first character (or byte) of the result. It must be a positive binary integer that is not greater than the length attribute of string. (The length attribute of a varying-length string is its maximum length.) |
length |
Denotes an optional argument that specifies the length of the result. If specified, length must be a binary integer in the range 0 to n, where n is the length attribute of string - start + 1. It must not, however, be the integer constant 0.
If string is a fixed-length string, omission of length is an implicit specification of LENGTH(string) - start + 1, which is the number of characters (or bytes) from the start character (or byte) to the last character (or byte) of string.
If string is a varying-length string, omission of length is an implicit specification of zero or LENGTH(string) - start + 1, whichever is greater. If the resulting length is zero, the result is the empty string. |
Example
First case:
Assume the column NAME (ALPHA(50)) has a value of 'KATIE AUSTIN'.
SUBSTRING(NAME, 7)
Returns the value 'AUSTIN'
SUBSTRING(NAME, 7, 1)
Returns the value 'A'.
Second case:
Select all rows from the PROJECT table for which the project name (PROJNAME) starts with the word 'OPERATION'.
CURSOR C1 PROJECT
*COND(SUBSTRING(PROJNAME,1,10) = 'OPERATION ')
The space at the end of the constant is necessary to preclude initial words such as 'OPERATIONS'.
List of SQL scalar functions by topic