SUBSTRING function [String]

Returns a substring of a string.

Syntax
{ SUBSTRING | SUBSTR } ( string-expression, start 
[, length ] )
Parameters
  • string-expression   The string from which a substring is to be returned.

  • start   The start position of the substring to return, in characters.

  • length   The length of the substring to return, in characters. If length is specified, the substring is restricted to that length.

Remarks

The function's behavior corresponds to ANSI/ISO SQL/2003 behavior:

  • Start value   The first character in the string is at position 1. A negative or zero start offset is treated as if the string were padded on the left with non-characters.

  • Length value   A positive length specifies that the substring ends length characters to the right of the starting position.

    A negative length returns an error.

    A length of zero returns an empty string.

If string-expression is of binary data type, the SUBSTRING function behaves as BYTE_SUBSTR.

To obtain characters at the end of a string, use the RIGHT function.

This function supports NCHAR inputs and/or outputs. Whenever possible, if the input string uses character length semantics the return value is described in terms of character length semantics.

See also
Standards and compatibility
  • SQL/2003   Core feature.

Example

The following table shows the values returned by the SUBSTRING function.

Example Result
SUBSTRING( 'front yard', 1, 4 ) fron
SUBSTRING( 'back yard', 6, 4 ) yard
SUBSTR( 'abcdefgh', 0, -2 ) Returns an error
SUBSTR( 'abcdefgh', -2, 2 ) Returns an empty string