The schema is SYSIBM.
The SUBSTR function returns a substring of a string.
The expression must return a value that is a built-in string, numeric, or datetime data type. If the value is not a string data type, it is implicitly cast to VARCHAR before evaluating the function. If string is either a character string or a binary string, a substring of string is zero or more contiguous bytes of string. If string is a graphic string, a substring of string is zero or more contiguous double-byte characters of string.
If length is explicitly specified, string is effectively padded on the right with the necessary number of blank characters (single-byte for character strings; double-byte for graphic strings) or hexadecimal zero characters (for BLOB strings) so that the specified substring of string always exists. The default for length is the number of bytes from the byte specified by the start to the last byte of string in the case of character string or binary string or the number of double-byte characters from the character specified by the start to the last character of string in the case of a graphic string. However, if string is a varying-length string with a length less than start, the default is zero and the result is the empty string. It must be specified as number of bytes in the context of the database code page and not the application code page. (For example, the column NAME with a data type of VARCHAR(18) and a value of 'MCKNIGHT' will yield an empty string with SUBSTR(NAME,10)).
If string is a character string, the result of the function is a character string represented in the code page of its first argument. If it is a binary string, the result of the function is a binary string. If it is a graphic string, the result of the function is a graphic string represented in the code page of its first argument. If the first argument is a host variable that is not a binary string and not a FOR BIT DATA character string, the code page of the result is the database code page. If any argument of the SUBSTR function can be null, the result can be null; if any argument is null, the result is the null value.
Table 1 shows that the result type and length of the SUBSTR function depend on the type and attributes of its inputs.
String Argument Data Type | Length Argument | Result Data Type |
---|---|---|
CHAR(A) | constant (l<255) | CHAR(l) |
CHAR(A) | not specified but start argument is a constant | CHAR(A-start+1) |
CHAR(A) | not a constant | VARCHAR(A) |
VARCHAR(A) | constant (l<255) | CHAR(l) |
VARCHAR(A) | constant (254<l<32673) | VARCHAR(l) |
VARCHAR(A) | not a constant or not specified | VARCHAR(A) |
CLOB(A) | constant (l) | CLOB(l) |
CLOB(A) | not a constant or not specified | CLOB(A) |
GRAPHIC(A) | constant (l<128) | GRAPHIC(l) |
GRAPHIC(A) | not specified but start argument is a constant | GRAPHIC(A-start+1) |
GRAPHIC(A) | not a constant | VARGRAPHIC(A) |
VARGRAPHIC(A) | constant (l<128) | GRAPHIC(l) |
VARGRAPHIC(A) | constant (127<l<16337) | VARGRAPHIC(l) |
VARGRAPHIC(A) | not a constant | VARGRAPHIC(A) |
DBCLOB(A) | constant (l) | DBCLOB(l) |
DBCLOB(A) | not a constant or not specified | DBCLOB(A) |
BLOB(A) | constant (l) | BLOB(l) |
BLOB(A) | not a constant or not specified | BLOB(A) |
Note: The LONG VARCHAR and LONG VARGRAPHIC data types continue to be supported but are deprecated and not recommended.
If string is a fixed-length string, omission of length is an implicit specification of LENGTH(string) - start + 1. If string is a varying-length string, omission of length is an implicit specification of zero or LENGTH(string) - start + 1, whichever is greater.
SUBSTR(:NAME, :SURNAME_POS)
Returns
the value 'JAY' SUBSTR(:NAME, :SURNAME_POS,1)
Returns
the value 'J'. SELECT * FROM PROJECT
WHERE SUBSTR(PROJNAME,1,10) = 'OPERATION '
The
space at the end of the constant is necessary to preclude initial
words such as 'OPERATIONS'.