MySQL SUBSTRING Function?

Summary: in this tutorial, we will introduce you to the MySQL SUBSTRING function that extracts a substring from a string.

The SUBSTRING function returns a substring with a given length from a string starting at a specific position. MySQL provides various forms of the substring function.

We will examine each form of the SUBSTRING function in the following sections.

MySQL SUBSTRING with position parameter

The following illustrates the first form the SUBSTRING function.

1 2	SUBSTRING(string,position); SUBSTRING(string FROM position);

There are two arguments:

1. The string argument is the string that you extract the substring.
2. The position argument is an integer that specifies the starting character of the substring. The position can be a positive or negative integer.

If the position is positive, the SUBSTRING function extracts the substring from the start of the string. See the following string.

For example, to get the ” SUBSTRING” out of the ” MySQL SUBSTRING” string, the position of the substring must be 7 as the following SELECT statement:

1	SELECT SUBSTRING('MYSQL SUBSTRING', 7);

If the position is negative, the SUBSTRING function extracts the substring from the end of the string. See the following ” MYSQL SUBSTRING” string:

To get the ” SUBSTRING” out of the ” MySQL SUBSTRING” using a negative position, you must pass -10 to the position argument as follows:

1	SELECT SUBSTRING('MySQL SUBSTRING',-10);

Notice that if the position is zero, the SUBSTRING function returns an empty string:

1	SELECT SUBSTRING('MYSQL SUBSTRING', 0);

Besides the MySQL-specific syntax, you can use SQL-standard syntax with the FROM keyword to call the SUBSTRING function.

For example, the following statement gets the SUBSTRING from the MySQL SUBSTRING string using the SQL-standard syntax:

1	SELECT SUBSTRING('MySQL SUBSTRING' FROM -10);

MySQL SUBSTRING with position and length

If 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:

1	SUBSTRING(string,position,length);

The following is the SQL-standard version of above statement, which is longer but more expressive.

1	SUBSTRING(string FROM position FOR length);

Besides the string and position arguments, the SUBSTRING function has an additional length argument. The length is a positive integer that specifies the number of characters of the substring.

If the sum of position and length is greater than the number of characters of the string, the SUBSTRINGfunction returns a substring starting from the position to the end of the string.

For example, to get the ” MySQL” from the ” MySQL SUBSTRING“, you use the following statement:

1	SELECT SUBSTRING('MySQL SUBSTRING',1,5);

Or

1	SELECT SUBSTRING('MySQL SUBSTRING' FROM 1 FOR 5);

In case you want to use the negative position, you use the following statement:

1	SELECT SUBSTRING('MySQL SUBSTRING',-15,5);

Or with the FROM FOR syntax:

1	SELECT SUBSTRING('MySQL SUBSTRING' FROM -15 FOR 5);

The SUBSTR() is the synonym for the SUBSTRING() so you can use both of them interchangeably.

In this tutorial, you have learned about the SUBSTRING function that extracts a substring with a given length from a string starting at a specific position.