MySQL Substring函数

Summary: in this tutorial, we will introduce you to MySQL substring function that allows you to extract a substring out of a string with various options.

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

MySQL substring function with position

Let’s take a look at a simple form of the SUBSTRING function:

SUBSTR(string,position)

This SUBSTRING  function returns a substring from the string starting from the position. The returned substring starts from the position position to the end of the string.

In the  SUBSTRING string, if you extract a substring from position 1, you get the whole string; if you extract a substring from position 6, you get a substring starting from position 6 to the end of the string.

SELECT SUBSTRING('MySQL SUBSTRING',1)

SELECT SUBSTRING('MySQL SUBSTRING',6)

The standard SQL syntax of the substring function is as follows:

SUBSTRING(string FROM position)

In this form, the SUBSTRING function uses the FROM keyword, which is more intuitive than the previous one. You can use this form of the SUBSTRING function to rewrite the above examples as the following statements:

SELECT SUBSTRING('MySQL SUBSTRING' FROM 1); SELECT SUBSTRING('MySQL SUBSTRING' FROM 6);

The results are the same as above.

MySQL substring function with position and length

In case 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)

This SUBSTRING function returns a substring starting from the position in a given length.In this form of the function, you pass an additional length parameter to the function. See the following example:

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

The following is the SQL standard version of the SUBSTRING function, which is longer but more expressive.

SUBSTRING(string FROM position FOR length)

Take a look at the following example:

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

MySQL substring function with negative position

All the SUBSTRING functions accept not only positive but also negative position. If you use a negative position, the SUBSTRING function will extract the substring from position characters from the end of the string.

See the following examples:

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

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

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

In this tutorial, we have shown you various forms of the MySQL SUBSTRING function to get a substring starting at a specific position in a given length.

原文链接：http://outofmemory.cn/mysql/function/mysql-substring