>>-+-SUBSTRING--(--expression--,--start--+-----------+--)-------+->< | '-,--length-' | '-SUBSTRING--(--expression--FROM--start--+--------------+--)-' '- FOR--length-'
The SUBSTRING function returns a substring of a string.
Expression must be any built-in numeric or string data type. A numeric argument is cast to a character string before evaluating the function. For more information on converting numeric to a character string, see VARCHAR. If expression is a character string, the result of the function is a character string. If it is a graphic string, the result of the function is a graphic string. If it is a binary string, the result of the function is a binary string.
A substring of expression is zero or more contiguous characters of expression. If expression is a graphic string, a character is a DBCS or Unicode graphic character. If expression is a character string, a character is a character that may consist of one or more bytes. If expression is a binary string, a character is a byte.
If expression is a UTF-8 or UTF-16 string, the query cannot contain:
If length is explicitly specified, padding is not performed.
If expression is a fixed-length string, omission of length is an implicit specification of LENGTH(expression) - start + 1, which is the number of characters (or bytes) from the start character (or byte) to the last character (or byte) of expression. If expression is a varying-length string, omission of length is an implicit specification of zero or LENGTH(expression) - start + 1, whichever is greater. If the resulting length is zero, the result is the empty string.
The data type of the result depends on the data type of expression:
Data type of expression | Data Type of the Result for SUBSTRING |
---|---|
CHAR or VARCHAR | VARCHAR |
CLOB | CLOB |
GRAPHIC or VARGRAPHIC | VARGRAPHIC |
DBCLOB | DBCLOB |
BINARY or VARBINARY | VARBINARY |
BLOB | BLOB |
The length attribute of the result is the same as the length attribute of expression. (Remember that if the actual length of expression is less than the value for start, the actual length of the substring is zero.)
If any argument can be null, the result can be null; if any argument is null, the result is the null value.
The CCSID of the result is the same as that of expression.
SELECT SUBSTRING(:NAME, :SURNAME_POS) FROM SYSIBM.SYSDUMMY1Returns the value 'AUSTIN'.
SELECT SUBSTRING(:NAME, :SURNAME_POS, 1) FROM SYSIBM.SYSDUMMY1Returns the value 'A'.
SELECT * FROM PROJECT WHERE SUBSTRING(PROJNAME,1,10) = 'OPERATION 'The space at the end of the constant is necessary to preclude initial words such as 'OPERATIONS'.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.