MySQL Substring Function 1

MySQL Substring

The MySQL Substring function is one of the String Functions that return a substring (a specified number of characters) from a given string. This article shows you how to write String Substring with an example.

The Substring function allows you to use negative values as the Position argument. If you use negative values, then it looks from right to left (or end position to start position). The basic syntax of the String Substring in MySQL is as shown below. For example, TutorialGateway, Position = 5, and Length = 10

-- Starts at r and returns until it reaches to end 
SELECT SUBSTRING(String, Position) FROM Source

-- Starts at r and end at a 
SELECT SUBSTRING(Str, Position, Length)

-- Same as First one but it is standard one
SELECT SUBSTRING(Str FROM Position)

-- Same as Second 
SELECT SUBSTRING(Str FROM Position FOR Length)
  • Str: A valid string or expression from which to want to extract the sub-string.
  • Position: From which index position do you want to start selecting characters?
  • Length: No of characters (character length) you want to extract from String.

MySQL Substring Function Example

The following query shows multiple ways to use this Sub-string function.

In the first statement, we used Sub string to start at index position 7

In the third line, we set the MySQL third argument (length) as 17. It means Sub string starts at position 7 and returns 17 characters from position 7

SELECT SUBSTRING('Learn MySQL at Tutorial Gateway', 7) AS Substring1;

SELECT SUBSTRING('Learn MySQL at Tutorial Gateway' FROM 16) AS Substring2;

SELECT SUBSTRING('Learn MySQL at Tutorial Gateway', 7, 17) AS Substring3;

SELECT SUBSTRING('Learn MySQL at Tutorial Gateway' FROM 4 FOR 15) AS Substring4;
Substring Example 1

MySQL Substring Example 2

In this String Function example, we use the negative values as the position argument.

SELECT SUBSTRING('Tutorial Gateway Provides MySQL Tutorial', -14) AS Substring1;

SELECT SUBSTRING('Tutorial Gateway Provides MySQL Tutorial' FROM -20) AS Substring2;

SELECT SUBSTRING('Tutorial Gateway Provides MySQL Tutorial', -14, 5) AS Substring3;

SELECT SUBSTRING('Tutorial Gateway Provides MySQL Tutorial' FROM -23 FOR 14) AS Substring3;
Example 2

In the below statement, We used this one to start at index position -14. It means this function starts the index position from the right side (i.e., l) and counts until it reaches 14. next, it starts returning those 14 characters

SELECT SUBSTRING('Tutorial Gateway Provides MySQL Tutorial', -14) AS Substring1;

Example 3

The Substring function also allows you to select the required number of characters from the column values. For this, We are going to use the below-shown data.

Employee Table 3

In this example, we are going to use this string substring function on the Email column.

SELECT FirstNme
      ,LastName
      ,DepartmentName
      ,Email
      ,SUBSTRING(Email, 5) AS Email1
      ,SUBSTRING(Email, FROM 7) AS Email2
      ,SUBSTRING(Email, -8) AS Email3
 FROM `mysql tutorial`.employe;
MySQL Substring Function 4