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

The schema is SYSIBM.

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 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.
In a Unicode database, if the value is a graphic data type, it is implicitly cast to a character string data type before the function is evaluated.
Any number (zero or more) contiguous bytes of this expression constitute a substring of this expression.

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 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
• Less than or equal to zero, the result of SUBSTRB is a zero length string

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

Result

If string is a CHAR or VARCHAR data type, the result of the function is a VARCHAR data type.
If string is a CLOB, the result of the function is a CLOB.
If string is a BINARY or VARBINARY data type, the result of the function is a VARBINARY data type.
If string 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.

The length attribute of the result is the same as the length attribute of the first argument unless both start and length arguments are specified and defined as constants.
In this case, the length attribute of the result is determined as follows:

• If length is a constant which is less than or equal to zero, the length attribute of the result is zero.
• If start is not a constant, but length is a constant, the length attribute of the result is the minimum of the length attribute of the first argument and length.
• If start is a constant, but length is not a constant or not specified, the length attribute of the result is the length attribute of the first argument minus the start position, plus one.
• If start and length are constants, the length attribute of the result is the minimum of the following values:
  • length
  • The length attribute of the first argument minus the start position plus one

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 previously mentioned result definitions, 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

1. Assume the host variable NAME (VARCHAR(50)) has a value of 'BLUE JAY' and the host variable SURNAME_POS (INTEGER) has a value of 6.

      SUBSTRB(:NAME, :SURNAME_POS)

   Returns the value 'JAY'.

      SUBSTRB(:NAME, :SURNAME_POS,1)

   Returns the value 'J'.


2. Select all rows from the PROJECT table which end in 'ING'.

      SELECT * 
      FROM   PROJECT
      WHERE  SUBSTRB(PROJNAME,-3) = 'ING'