Returns the substring of the character string corresponding to the first argument starting at start_pos and length characters long. If the third argument length is not specified, the substring starting at start_pos up to the end of char_expression is returned.

Syntax

SUBSTR ( char_expression, start_pos[ , length] )

Notes

  • The first argument must be of type CHARACTER. It can be any meaningful character value (for example, a literal expression, database column, or parameter).
  • The second argument must be of type INTEGER. It indicates the starting position from which the substring result is extracted.
  • The third argument, if specified, must be of type INTEGER. It indicates the number of characters the substring function will extract.
  • The values for specifying position in the character string start from 1. The first character in a string is at position 1, the second character is at position 2, and so on.
  • The result is of type CHARACTER.
  • If any of the argument expressions evaluate to NULL, the result is NULL.
  • char_expression and the result can contain multi‑byte characters.
  • If the value of start_pos is:
    • Smaller than 0, the function returns a "Bad argument" error
    • Bigger than the actual length of the string value, the function returns an empty zero length substring
  • If the value of length is:
    • Smaller than 0, the function returns a "Bad argument" error.
    • Bigger than the actual length of the substring (from start_pos to the end of the literal), the function returns the substring from start_pos to the end of the literal.
    • Bigger than the actual length of the substring (from start_pos up to the end of the column's row data), the function returns the substring from the start_pos to the end of the column's row data. The function returns this, even when the substring result exceeds the column's SQL width.
    • Bigger than 0 and the column's row data exceeds the column's SQL width, the function returns the substring.
  • If length is not specified, the function returns the substring from start_pos to the end of the literal.
  • The function returns the end of the column's row data if the length of the substring is not bigger than the column's SQL width.Otherwise, the function returns the substring truncated to the column's SQL width.

Example

This example illustrates the SUBSTR function:

SELECT last_name, '(', SUBSTR (phone, 1, 3) , ')',
          SUBSTR (phone, 4, 3), '-', 
          SUBSTR (phone, 7, 4)
  FROM customer ;

Compatibility

Progress extension