8.10.2 SUBSTRB

(1) Specification format

scalar-function-SUBSTRB ::= SUBSTRB(source-binary-data, start-position[,number-of-bytes-to-extract])
 
  source-binary-data ::= value-expression
  start-position ::= value-expression
  number-of-bytes-to-extract ::= value-expression

To Page Top

(2) Explanation of specification format

source-binary-data:

Specifies the source binary data.

The following rules apply:

• Specify the source binary data in the form of a value expression. For details about value expressions, see 7.20 Value expression.

• Specify BINARY or VARBINARY type data for the source binary data.

• You cannot specify a dynamic parameter by itself for the source binary data.

start-position:

Specifies the starting byte position from which binary data is to be extracted.

If you specify a value greater than or equal to 0 for the start position, the value represents the position from the beginning of the source binary data. For example, if the start position is 2, the extraction will start at the second byte.

If you specify a negative value for the start position, the value represents a position from the end of the source binary data. For example, if the start position is -2, the extraction will start at the second byte from the end.

The following rules apply:

• Specify the start position in the form of a value expression. For details about value expressions, see 7.20 Value expression.

• Specify an integer for the start position (INTEGER or SMALLINT type data).

• If you specify 0 for the start position, a start position of 1 is assumed.

• If a dynamic parameter is specified by itself for the start position, the assumed data type of the dynamic parameter will be INTEGER.

number-of-bytes-to-extract:

Specifies the length of the binary data to extract.

The following rules apply:

• Specify number-of-bytes-to-extract in the form of a value expression. For details about value expressions, see 7.20 Value expression.

• Specify an integer greater than or equal to 0 (data of type INTEGER or SMALLINT) for number-of-bytes-to-extract.

• If you specify a dynamic parameter by itself for number-of-bytes-to-extract, the assumed data type of the dynamic parameter is INTEGER.

The following examples illustrate the result of executing the scalar function SUBSTRB.

Examples:

• Extract three bytes starting from the second byte from the beginning of the binary data X'ABCDEF1234567890'.

  SUBSTRB(X'ABCDEF1234567890',2,3) → X'CDEF12'

• Extract two bytes starting from the third byte from the end of the binary data X'ABCDEF1234567890'.

  SUBSTRB(X'ABCDEF1234567890',-3,2) → X'5678'

To Page Top

(3) Rules

1. The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).

2. In the following cases, the execution result will be a null value:

   • If number-of-bytes-to-extract has a negative value (the result will be the null value regardless of what is specified for the source binary data or the start position)

   • If the source binary data, start position, or number of bytes to extract is a null value

3. The data type and data length of the execution result are shown in the following table.

   Table 8‒33: Data type and data length of the execution result of the scalar function SUBSTRB

   Data type and data length of the source binary data	Data type and data length of the execution result
   BINARY(n)	VARBINARY(n)
   VARBINARY(n)

   Legend:

   n: Maximum length of the source binary data

4. The following table shows the number of bytes of binary data that can be extracted by the scalar function SUBSTRB.

   Table 8‒34: Number of bytes of binary data that can be extracted by the scalar function SUBSTRB

   Specification of the scalar function SUBSTRB		Number of bytes of binary data that can be extracted
   Specification of number of bytes to extract	Value specified for start position
   Specified	Positive value	MAX{0, MIN (number of bytes to extract, number of bytes in source binary data - start position + 1)}
   	0	MIN(number of bytes to extract, number of bytes in source binary data)
   	Negative value	MIN(number of bytes to extract, absolute value of the start position, number of bytes in source binary data)
   Omitted	Positive value	MAX(0, number of bytes in source binary data - start position + 1)
   	0	number of bytes in source binary data
   	Negative value	MIN(absolute value of the start position, number of bytes in source binary data)

5. In the following cases, the execution result will be data whose actual length is 0 bytes:

   • If the execution result is binary data of length 0

   • If the actual length of the source binary data is 0 bytes

   • If the specified start position satisfies either of the following inequalities:

     start position > number of bytes in source binary data

     start position < -number of bytes in source binary data

6. If the number of bytes in the source binary data, starting from the start position, is less than the number of bytes to extract, all of the source binary data, starting from the start position, is returned.

   Example:

   SUBSTRB(X'ABCDEF',2,5) → X'CDEF'

To Page Top