MySQL Substring Function | MySQL Tutorials

The MySQL substring function is used to return a portion of a string of data.

This function may be useful, for example, in a situation where you want to grab a portion of a string of data, either before storing it in the database, or after retrieving it from the database.

In this tutorial, we will look at how to use the MySQL substring function to retrieve substrings as well as the 4 syntaxes for the function.

MySQL Substring Syntax

There are 4 main syntaxes for the MySQL substring function:

-- These take 2 parameters and do the same thing
SUBSTRING(string, position)
SUBSTRING(string FROM position)
 
-- These take 3 parameters and do the same thing
SUBSTRING(string, position, length)
SUBSTRING(string FROM position FOR length)

So you can specify just the starting position, or you can specify the starting position and the length of the string that you want returned.

Also, you should notice that you can either use the comma notation for separating the parameters or you can use the word notation.

MySQL Substring Examples

Here are some examples of using the MySQL substring function:

Below we are returning a substring starting from the 10th character:

SELECT SUBSTRING('Tutorial Arena', 10); -- gives 'Arena'

SELECT SUBSTRING('Tutorial Arena' FROM 10); -- gives 'Arena'

Below we are returning a substring 3 characters long starting at the 10th character:

SELECT SUBSTRING('Tutorial Arena', 10, 3); -- gives 'Are'

SELECT SUBSTRING('Tutorial Arena' FROM 10 FOR 3); -- gives 'Are'

As we can see in the examples above, both notations are functionally identical.

MySQL Substring from right (with a negative start position)

We can use a negative number for the start position. In this case, the starting point will be treated as that many characters from the end of the string.

Look at this example:

SELECT SUBSTRING('Tutorial Arena', -5); -- gives 'Arena'

SELECT SUBSTRING('Tutorial Arena', -5, 3); -- gives 'Are'

Once again, you could use the alternate notation and get the same results.

Things to note

MySQL treats the first character in a string as character number one (1). This is different from how it is treated in programming languages like PHP and Python which treat the first character as character zero (0). You will need to keep this in mind when doing substrings in MySQL or you are going to get unexpected errors.

That is it for this tutorial on MySQL substrings. We hope you found it useful.