SUBSTRB scalar function

The schema is SYSIBM.

The SUBSTRB function returns a substring of a string, beginning at a specified position in the string. Lengths are calculated in bytes.

The SUBSTRB function is available starting with version 9.7 Fix Pack 1.

string

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

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. A substring of string is zero or more contiguous bytes of string. In a Unicode database, if the value is a graphic data type, it is implicitly cast to a character string data type before evaluating the function.

start

An expression that specifies the start position in string of the beginning of the result substring. 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.

If start is positive, then the start position is calculated from the beginning of the string. If start is greater than the length of string, then a zero length string is returned.

If start is negative, then the start position is calculated from the end of the string and by counting backwards. If the absolute value of start is greater than the length of string, then a zero length string is returned.

If start is 0, then a start position of 1 is used.

length

An expression that specifies the length of the result in bytes. 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.

If the value of length is greater than the number of bytes from the start position to the end of the string, the result length is the length of the first argument minus the start position plus one.

If the value of length is less than or equal to zero, the result of SUBSTRB is a zero length string.

The default for length is the number of bytes from the position specified by start to the last byte of string.

If string is a CHAR or VARCHAR data type, the result of the function is a VARCHAR data type. If it is a CLOB, the result of the function is a CLOB. If it is a BLOB, the result of the function is a BLOB. 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 section code page; otherwise, it is the code page of the first argument.

If any argument of the SUBSTRB function can be null, the result can be null; if any argument is null, the result is the null value.

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 above, the semantics imply that if string is a mixed single-byte and multi-byte character string, the result might contain fragments of multi-byte characters, depending on 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 SUBSTRB function will detect these partial characters and will replace each byte of an incomplete character with a single blank character.
• SUBSTRB is similar to the existing SUBSTR function, with the following exceptions:
  • SUBSTRB supports a negative start value, which indicates the processing should start from the end of the string.
  • SUBSTRB allows length to be greater than the calculated result length. In this case, a shorter string will be returned, rather than returning an error.
  • Graphic input data is not natively supported for the first argument of SUBSTRB. In a Unicode database, graphic data is supported, but it is first converted to character data before evaluating the function, and lengths are calculated in bytes.
  • The result data type of SUBSTRB is VARCHAR if the input data type is CHAR.
  • The length attribute of the result for SUBSTRB is either the same as the length attribute of the first argument, or it is derived based on the start or length attributes, if either of these are constants.

Examples