Tutorial Gateway

MySQL Substring

by

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 basic syntax of the String Substring in MySQL is as shown below. For example, String is 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 (String, Position, Length) FROM Source

-- Same as First one but it is standard SQL Syntax
SELECT SUBSTRING (String FROM Position) FROM Source

-- Same as Second 
SELECT SUBSTRING (String FROM Position FOR Length) FROM Source
  • String: 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.

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).

MySQL Substring Function Example

The following query shows multiple ways to use this Substring function.

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;

OUTPUT

MySQL Substring Function 1

ANALYSIS

In the below statement, we used Substring to start at index position 7

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

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

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

MySQL Substring Example 2

In this String Function example, we use the negative values as the position argument in a substring function.

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;

OUTPUT

MySQL Substring Function 2

In the below statement, We used Substring to starting at index position -14. It means, the substring 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 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;

OUTPUT

MySQL Substring Function 4