MySQL Substring Function 1

MySQL Substring

The MySQL Substring function is one of the String Functions used to return a substring (a specified number of characters) from a given string. In this article, we show you how to write String Substring in MySQL with example.

The Substring function in MySQL allows you to use negative values as the Position argument. If you use negative values, then substring 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 sub-string.
  • Position: From which index position, you want to start selecting the 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 start 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;
MySQL Substring Function 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;
MySQL Substring Function 2

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

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

String Substring Example 3

The MySQL String 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

MySQL Substring Function 3

In this example, We are going to use this string sub string 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