SUBSTR | IBM Db2

The SUBSTR function returns a substring of a string.

string

The input expression, which specifies the string from which the substring is to be derived. The expression must return a value that is a built-in character string, numeric value, Boolean value, or datetime value. If the value is not a character string, it is implicitly cast to VARCHAR before the function is evaluated. Any number (zero or more) contiguous string units of this expression constitute a substring of this expression.

start

An expression that specifies the position, relative to the beginning of the input expression, from which the substring is to be calculated. For example:

• Position 1 is the first string unit of the input expression. The statement SUBSTR('abcd',1,2) returns 'ab'.
• Position 2 is one position to the right of position 1. The statement SUBSTR('abcd',2,2) returns 'bc'.

The expression must return a built-in numeric, CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC value. If the value is not of type INTEGER, it is implicitly cast to INTEGER before evaluating the function.

length

An expression that specifies the length of the result. If specified, the expression must return a value that is a built-in numeric, CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type. If the value is not of type INTEGER, it is implicitly cast to INTEGER before evaluating the function. The value of the integer must be in the range of 0 to n, where n equals (the length attribute of string in string units) - start + 1 (SQLSTATE 22011 if out of range).

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 binary strings) so that the specified substring exists. The default length is the number of string units from the string unit specified by start to the last string unit of 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 string units 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)).