MySQL | SUBSTRING method
Start your free 7-days trial now!
MySQL's SUBSTRING(~) method returns a substring from the input string starting at the specified position.
Syntax
SELECT SUBSTRING(str, pos);SELECT SUBSTRING(str FROM pos);SELECT SUBSTRING(str, pos, len);SELECT SUBSTRING(str FROM pos FOR len);
Parameters
1. str | string
The string to return substring from.
2. pos | integer
The position that serves as the start of the substring. Negative values indicate positions from the end of the string.
3. len | integer
The length of the returned substring. A length < 0 returns an empty string.
Examples
Basic usage
To return the substring starting from position 4:
SELECT SUBSTRING('SkyTowner', 4);
+---------------------------+| SUBSTRING('SkyTowner', 4) |+---------------------------+| Towner |+---------------------------+
We return the substring starting from the 4th character of 'SkyTowner'.
Negative position
To return the substring starting from 4th position from end of string:
SELECT SUBSTRING('SkyTowner' FROM -4);
+--------------------------------+| SUBSTRING('SkyTowner' FROM -4) |+--------------------------------+| wner |+--------------------------------+
The substring that is returned starts from 'w' which is the 4th character from the end of 'SkyTowner'.
Length of substring
To return a substring that is 3 characters long starting from position 1:
SELECT SUBSTRING('SkyTowner', 1, 3);
+------------------------------+| SUBSTRING('SkyTowner', 1, 3) |+------------------------------+| Sky |+------------------------------+
Negative length
Negative length returns an empty string:
SELECT SUBSTRING('SkyTowner' FROM 4 FOR -2);
+--------------------------------------+| SUBSTRING('SkyTowner' FROM 4 FOR -2) |+--------------------------------------+| |+--------------------------------------+
Although it is hard to see, the returned value is an empty string.
