MySQL Substring
Summary: in this tutorial, we will introduce you to MySQL substring function that allows you to extract a substring out of a string with various options.
The SUBSTRING function returns a substring from a string starting at a specific position with a given length. MySQL provides various forms of the substring function.
MySQL substring function with position
Let’s take a look at a simple form of the SUBSTRING function:
SUBSTRstringposition |
This SUBSTRING function returns a substring from the string starting from the position. The returned substring starts from the position position to the end of the string.
In the SUBSTRING string, if you extract a substring from position 1, you get the whole string; if you extract a substring from position 6, you get a substring starting from position 6 to the end of the string.
SELECTSUBSTRING'MySQL SUBSTRING' |
SELECTSUBSTRING'MySQL SUBSTRING' |
The standard SQL syntax of the substring function is as follows:
SUBSTRINGstringposition |
In this form, the SUBSTRING function uses the FROM keyword, which is more intuitive than the previous one. You can use this form of the SUBSTRING function to rewrite the above examples as the following statements:
SELECTSUBSTRING('MySQL SUBSTRING'1);
SELECTSUBSTRING('MySQL SUBSTRING'6);
|
The results are the same as above.
MySQL substring function with position and length
In case you want to specify the length of the substring that you want to extract from a string, you can use the following form of the SUBSTRING function:
SUBSTRINGstringpositionlength |
This SUBSTRING function returns a substring starting from the position in a given length.In this form of the function, you pass an additional length parameter to the function. See the following example:
SELECTSUBSTRING'MySQL SUBSTRING' |
The following is the SQL standard version of the SUBSTRING function, which is longer but more expressive.
SUBSTRINGstringpositionlength |
Take a look at the following example:
SELECTSUBSTRING'MySQL SUBSTRING' |
MySQL substring function with negative position
All the SUBSTRING functions accept not only positive but also negative position. If you use a negative position, the SUBSTRING function will extract the substring from position characters from the end of the string.
See the following examples:
SELECTSUBSTRING'MySQL SUBSTRING'
SELECTSUBSTRING'MySQL SUBSTRING'
|
SELECTSUBSTRING'MySQL SUBSTRING'
SELECTSUBSTRING'MySQL SUBSTRING'
|
The SUBSTR() is synonym for the SUBSTRING() so you can use both of them interchangeably.
In this tutorial, we have shown you various forms of the MySQL SUBSTRING function to get a substring starting at a specific position in a given length.