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:
- The
string
specifies the string that you extract the substring. - The
position
is an integer that specifies the starting character of the substring. Theposition
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.