SUBSTR scalar function
The SUBSTR function returns a substring of a string.
The schema is SYSIBM.
- 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'.
- Position 1 is the first string unit of the input expression. The statement
- 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)).If string is:- A fixed-length string, the default length is
LENGTH(string) - start + 1 - A varying-length string, the default length is either zero or
LENGTH(string) - start + 1, whichever is greater.
- A fixed-length string, the default length is
Result
If string is a character string, the result is a character string represented in the code page and string units of its first argument. If it is a binary string, the result is a binary string. If it is a graphic string, the result is a graphic string represented in the code page and string units 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<n) If the units of string are:
|
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<n) If the units of string are:
|
CHAR(l) |
| VARCHAR(A) | constant (m<l<n) If the units of string are:
|
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<n) If the units of string are:
|
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<n) If the units of string are:
|
GRAPHIC(l) |
| VARGRAPHIC(A) | constant (m<l<n) If the units of string are:
|
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) |
| BINARY(A) | constant (l<256) | BINARY(l) |
| BINARY(A) | not specified but start argument is a constant | BINARY(A-start+1) |
| BINARY(A) | not a constant | VARBINARY(A) |
| VARBINARY(A) | constant (l<256) | BINARY(l) |
| VARBINARY(A) | constant (255<l<32673) | VARBINARY(l) |
| VARBINARY(A) | not a constant or not specified | VARBINARY(A) |
| BLOB(A) | constant (l) | BLOB(l) |
| BLOB(A) | not a constant or not specified | BLOB(A) |
Notes
- In dynamic SQL, string, start, and length can be represented by a parameter marker. If a parameter marker is used for string, the data type of the operand will be VARCHAR, and the operand will be nullable.
- Though not explicitly stated in the result definitions mentioned previously, the semantics imply that if string is a mixed single- and multi-byte character string, the result might contain fragments of multi-byte characters, depending upon the values of start and length. For example, the result could possibly begin with the second byte of a multi-byte character, or end with the first byte of a multi-byte character. The SUBSTR function does not detect such fragments, nor provide any special processing should they occur.
Examples
- Example 1: Assume that the host variable
NAME (VARCHAR(50))has the value'BLUE JAY':- The following statement returns the value
'BLUE':SUBSTR(:NAME,1,4) - The following statement returns the value
'JAY':SUBSTR(:NAME,6) - The following statement returns the value
'JA':SUBSTR(:NAME,6,2)
- The following statement returns the value
- Example 2: Select all rows from the PROJECT table for which the project name (PROJNAME)
starts with the word 'OPERATION'.
The space at the end of the constant is necessary to exclude words such as 'OPERATIONAL'.SELECT * FROM PROJECT WHERE SUBSTR(PROJNAME,1,10) = 'OPERATION '