MySQL SUBSTR() Function

In MySQL, the The SUBSTR() function returns a substring of a specified string starting starting at a specified position. SUBSTR() is the same as SUBSTRING().

SUBSTR() Syntax

MySQL the SUBSTR() function have 4 forms of syntax:

SUBSTR(str, pos)
SUBSTR(str FROM pos)
SUBSTR(str, pos, len)
SUBSTR(str FROM pos FOR len)

MID(str, pos, len) is equivalent to SUBSTR(str, pos, len).

Parameters

str

Required. The original string from which to extract the substring.

pos

Required. The location to start extracting a substring. It can be positive or negative. If it is negative, the beginning of the substring is pos characters from the end of the string, rather than the beginning.

len

Optional. The length (number of characters) of the substring. If not specified, extract to the end of the original string.

Return value

The SUBSTR() function returns a substring of a specified string starting starting at a specified position.

• If pos eqauls or exceeds the length of the original string, the function will return an empty string ''.
• If pos is negative, the function returns an empty string ''.
• If the parameter is NULL, the function will return NULL.

SUBSTR() Examples

SELECT
    SUBSTR('Hello', 1, 2),
    SUBSTR('Hello', 2, 2),
    SUBSTR('Hello', 1),
    SUBSTR('Hello', 1, 8),
    SUBSTR('Hello', -2, 2),
    SUBSTR('Hello', 8, 2),
    SUBSTR('Hello', 0, 2),
    SUBSTR('Hello', 0),
    SUBSTR('Hello' FROM 2 FOR 2)\G

       SUBSTR('Hello', 1, 2): He
       SUBSTR('Hello', 2, 2): el
          SUBSTR('Hello', 1): Hello
       SUBSTR('Hello', 1, 8): Hello
      SUBSTR('Hello', -2, 2): lo
       SUBSTR('Hello', 8, 2):
       SUBSTR('Hello', 0, 2):
          SUBSTR('Hello', 0):
SUBSTR('Hello' FROM 2 FOR 2): el