Tutorial Gateway

  • C
  • C#
  • Python
  • SQL
  • Java
  • JS
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs
  • MySQL

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

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;
MySQL Substring Function 1

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;
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;
MySQL Substring Function 4

Filed Under: MySQL

  • How to Download MySQL
  • Install MySQL on Windows
  • MySQL Create Database
  • MySQL Delete Database
  • MySQL Create Table
  • MySQL Drop Table
  • MySQL SELECT Statement
  • MySQL ALIAS Column
  • MySQL Distinct
  • MySQL Insert Statement
  • MySQL Delete
  • MySQL Truncate Table
  • MySQL WHERE Clause
  • MySQL Order By
  • MySQL Group By
  • MySQL Having Clause
  • MySQL LIMIT
  • MySQL Arithmetic Operators
  • MySQL COALESCE Function
  • MySQL AND Operator
  • MySQL NOT Operator
  • MySQL OR Operator
  • MySQL XOR Operator
  • MySQL BETWEEN Operator
  • MySQL Not Between Operator
  • MySQL GREATEST Function
  • MYSQL LEAST Function
  • MySQL LIKE Operator
  • MySQL NOT LIKE Operator
  • MySQL IFNULL Operator
  • MySQL NULLIF Operator
  • MySQL INTERVAL Operator
  • MySQL IS Operator
  • MySQL IN Operator
  • MySQL NOT IN Operator
  • MySQL IS NOT NULL
  • MySQL IS NULL
  • MySQL Inner Join
  • MySQL Cross Join
  • MySQL Right Join
  • MySQL Left Join
  • MySQL Aggregate Functions
  • MySQL Date Functions
  • MySQL Date Function
  • MySQL String Functions
  • MySQL Numeric Functions

Copyright © 2021· All Rights Reserved by Suresh.
About | Contact | Privacy Policy