SUBSTR

The SUBSTR function returns a substring of a string.

expression

An expression that specifies the string from which the result is derived.

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 about 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 byte.¹ If expression is a binary string, a character is a byte.

start

An expression that specifies the position within expression of the first character (or byte) of the result. The expression must return a value that is a built-in BIGINT, INTEGER, or SMALLINT data type. A value of 1 indicates that the first character of the result is the first character of expression. A negative or zero value indicates a position before the beginning of the string. It may also be greater than the length attribute of expression. (The length attribute of a varying-length string is its maximum length.)

length

An expression that specifies the length of the result. If specified, length must be an expression that returns a value that is a built-in BIGINT, INTEGER, or SMALLINT data type. The value must be greater than or equal to 0.

If length is explicitly specified, expression is effectively padded on the right with the necessary number of blank characters so that the specified substring of expression always exists. Hexadecimal zeroes are used as the padding character when expression is a binary string.

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 SUBSTR
CHAR or VARCHAR	CHAR, if: length is explicitly specified by an integer constant that is greater than zero. length is not explicitly specified, but expression is a fixed-length string and start is an integer constant. VARCHAR, in all other cases
CLOB	CLOB
GRAPHIC or VARGRAPHIC	GRAPHIC, if: length is explicitly specified by an integer constant that is greater than zero. length is not explicitly specified, but expression is a fixed-length string and start is an integer constant. VARGRAPHIC, in all other cases.
DBCLOB	DBCLOB
BINARY or VARBINARY	BINARY, if: length is explicitly specified by an integer constant that is greater than zero. length is not explicitly specified, but expression is a fixed-length string and start is an integer constant. VARBINARY, in all other cases.
BLOB	BLOB

If expression is not a LOB, the length attribute of the result depends on length, start, and the attributes of expression.

If length is explicitly specified by an integer constant that is greater than zero, the length attribute of the result is length.
If length is not explicitly specified, but expression is a fixed-length string and start is an integer constant, the length attribute of the result is LENGTH(expression) - start + 1.

In all other cases, 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.

Examples

Assume the host variable NAME (VARCHAR(50)) has a value of 'KATIE AUSTIN' and the host variable SURNAME_POS (INTEGER) has a value of 7.
```
  SELECT SUBSTR(:NAME, :SURNAME_POS)
    FROM SYSIBM.SYSDUMMY1
```
Returns the value 'AUSTIN'.

Likewise,

  SELECT SUBSTR(:NAME, :SURNAME_POS, 1)
    FROM SYSIBM.SYSDUMMY1

Returns the value 'A'.

Select all rows from the PROJECT table for which the project name (PROJNAME) starts with the word 'OPERATION '.
```
  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'.

¹ The SUBSTR function accepts mixed data strings. However, because SUBSTR operates on a strict byte-count basis, the result will not necessarily be a properly formed mixed data string.