Categories:
String & Binary Functions (Matching/Comparison)

SUBSTR , SUBSTRING

Returns the portion of the string or binary value from base_expr, starting from the character/byte specified by start_expr, with optionally limited length.

These functions are synonymous.

Collation:No impact
See also:LEFT , RIGHT

Syntax

SUBSTR( <base_expr>, <start_expr> [ , <length_expr> ] )

SUBSTRING( <base_expr>, <start_expr> [ , <length_expr> ] )

Usage Notes

  • If length_expr is used, up to length_expr characters/bytes are returned, otherwise all the characters until the end of the string or binary value are returned.

  • The values in start_expr start from 1:

    • If 0 is specified, it is treated as 1.
    • If a negative value is specified, the starting position is computed as the start_expr characters/bytes from the end of the string or binary value. If the position is outside of the range of a string or binary value, an empty value is returned.
  • If any of the inputs are NULL, NULL is returned.

Examples

SELECT SUBSTR('testing 1 2 3', 9, 5) FROM x;

-------------------------------+
 substr('testing 1 2 3', 9, 5) |
-------------------------------+
 1 2 3                         |
-------------------------------+

SELECT '123456', pos, len, SUBSTR('123456', pos, len) FROM o;

----------+--------+-----+----------------------------+
 '123456' |  pos   | len | substr('123456', pos, len) |
----------+--------+-----+----------------------------+
 123456   | -1     | 3   | 6                          |
 123456   | -3     | 3   | 456                        |
 123456   | -3     | 7   | 456                        |
 123456   | -5     | 3   | 234                        |
 123456   | -7     | 3   |                            |
 123456   | 0      | 3   | 123                        |
 123456   | 0      | 7   | 123456                     |
 123456   | 1      | 3   | 123                        |
 123456   | 3      | 3   | 345                        |
 123456   | 3      | 7   | 3456                       |
 123456   | 5      | 3   | 56                         |
 123456   | 5      | 7   | 56                         |
 123456   | 7      | 3   |                            |
 123456   | [NULL] | 3   | [NULL]                     |
 123456   | [NULL] | 7   | [NULL]                     |
----------+--------+-----+----------------------------+