Tutorial Gateway

MySQL Substring

by

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

SQL Substring Function Syntax

The basic syntax of the Substring string 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: Number of characters (character length) you want to extract from String.

The Substring function in MySQL allows you to use negative values for the Position argument. If you use negative values, then substring will look from right to left (or end position to start position).

MySQL Substring Function Example 1

The following query will show 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 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 third argument (length) as 17. It means, Substring will start at position 7 and return 17 character from position 7

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

MySQL Substring Example 2

In this example, we will use 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 below statement, We used Substring to start at index position -14. It means, function will start index position from right side (i.e., l) and count until it reaches 14. next, it will start returning those 14 charcaters

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

MySQL Substring Function Example 3

The MySQL String Substring function also allows you to select 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 substring function on 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

Thank You for Visiting Our Blog