MySQL SUBSTRING Function

Created with Sketch.

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:

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

Code language: SQL (Structured Query Language) (sql)

The substring() function has two parameters:

  1. The string specifies the string that you extract the substring.
  2. The position 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:

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

Code language: SQL (Structured Query Language) (sql)

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:

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

Code language: SQL (Structured Query Language) (sql)

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

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

Code language: SQL (Structured Query Language) (sql)

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:

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

Code language: SQL (Structured Query Language) (sql)

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:

SUBSTRING(string,position,length);

Code language: SQL (Structured Query Language) (sql)

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

SUBSTRING(string FROM position FOR length);

Code language: SQL (Structured Query Language) (sql)

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 SUBSTRING function 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:

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

Code language: SQL (Structured Query Language) (sql)

Or

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

Code language: SQL (Structured Query Language) (sql)

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

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

Code language: SQL (Structured Query Language) (sql)

Or with the FROM FOR syntax:

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

Code language: SQL (Structured Query Language) (sql)

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.

Leave a Reply

Your email address will not be published. Required fields are marked *