MySQL SUBSTRING() Function

Summary: in this tutorial, you will learn how to the MySQL SUBSTRING() function that extracts a substring from a string.

Introduction to MySQL SUBSTRING() function

The SUBSTRING() function allows you to extract a substring from a string. MySQL provides various forms of the substring function.

We will examine each form of the SUBSTRING function in the following sections. MID(): Extracts a substring from the middle of a string. The MID() function is a synonym for SUBSTRING().

MID(): Extracts a substring from the middle of a string. The MID() function is a synonym for SUBSTRING().

1) SUBSTRING() function 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)

In this syntax, the SUBSTRING() function has two parameters:

• string. The string from which you want to extract the substring.
• position. This 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. For example:

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

SELECT SUBSTRING('MYSQL SUBSTRING', 7);-- SUBSTRINGCode language: SQL (Structured Query Language) (sql)

Try It Out

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 pass -10 to the position argument as follows:

SELECT SUBSTRING('MySQL SUBSTRING',-10);--  SUBSTRINGCode language: SQL (Structured Query Language) (sql)

Try It Out

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)

Try It Out

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)

Try It Out

2) SUBSTRING function with position and length arguments

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 can be 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);-- MySQLCode language: SQL (Structured Query Language) (sql)

Try It Out

Or

SELECT SUBSTRING('MySQL SUBSTRING' FROM 1 FOR 5);-- MySQLCode language: SQL (Structured Query Language) (sql)

Try It Out

If you want to use the negative position, you use the following statement:

SELECT SUBSTRING('MySQL SUBSTRING',-15,5);-- MySQLCode language: SQL (Structured Query Language) (sql)

Try It Out

Or with the FROM FOR syntax:

SELECT SUBSTRING('MySQL SUBSTRING' FROM -15 FOR 5);-- MySQLCode language: SQL (Structured Query Language) (sql)

Try It Out

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

Summary

• Use the MySQL SUBSTRING() function to extract a substring from a string.