SUBSTR allows non-positive start values

When operating in NPS compatibility mode, the SUBSTR scalar function allows the start value to be negative, zero, or positive.

The start argument of the SUBSTR scalar function 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.
  • Position 2 is one position to the right of position 1.
  • Position 0 is one position to the left of position 1.
  • Position -1 is two positions to the left of position 1.

Which values are valid for the start argument is determined by the SQL_COMPAT global variable:

  • If SQL_COMPAT='NPS', the value can be any positive, zero, or negative number:
  • Otherwise, the value must be at least 1 and at most the maximum length of the input string (SQLSTATE 22011 if out of range). Also, it must be specified as a number of string units in the context of the database code page, not the application code page.

Examples

If SQL_COMPAT='NPS':
  • The statement SUBSTR('abcd',1,2) returns 'ab'.
  • The statement SUBSTR('abcd',2,2) returns 'bc'.
  • The statement SUBSTR('abcd',0,2) returns 'a'.
  • The statement SUBSTR('abcd',-1,2) returns a zero-length string.
  • The statement SUBSTR('BLUE JAY',0,4) returns 'BLU'.
  • The statement SUBSTR('BLUE JAY',-1,4) returns 'BL'.