MySQL SUBSTRING() Function

  1. Overview
  2. MYSQL
  3. String Functions
  4. MySQL SUBSTRING() Function

The SUBSTRING() function in MySQL extracts a substring from a string, starting at a specific position and optionally extracting a defined number of characters.

Syntax

SUBSTRING(string, start, length)

Or:

SUBSTRING(string FROM start FOR length)

Parameters

Parameter Description
string Required. The string from which the substring is to be extracted.
start Required. The position to start extracting from. It can be a positive or negative number.
length Optional. The number of characters to extract. If omitted, the function will return the substring from the start position to the end of the string.

Definition and Usage

  • The SUBSTRING() function extracts part of the string starting from the start position.
  • If start is positive, it extracts from the left of the string.
  • If start is negative, it extracts from the end of the string.
  • If length is provided, it defines the number of characters to extract. If omitted, the function extracts characters from the start position to the end of the string.

Return Values

  • The function returns the extracted substring.
  • If the start position exceeds the string length, it returns an empty string.

Usage Examples

Example 1: Extract a substring starting from position 5 and extracting 3 characters

SELECT SUBSTRING("SQL Tutorial", 5, 3) AS ExtractString;

Output:

ExtractString
Tut

Explanation: Starting from the 5th position ("T"), it extracts 3 characters: "Tut".

Example 2: Extract 5 characters from position 2 in the "CustomerName" column

SELECT SUBSTRING(CustomerName, 2, 5) AS ExtractString
FROM Customers;

Output: (Based on the data in CustomerName)

Explanation: The function extracts 5 characters starting from position 2 of each CustomerName.

Example 3: Extract a substring from the end of the string (start from position -5 and extract 5 characters)

SELECT SUBSTRING("SQL Tutorial", -5, 5) AS ExtractString;

Output:

ExtractString
orial

Explanation: Starting from the 5th position from the end, it extracts 5 characters: "orial".

Technical Details

  • Works In: From MySQL 4.0 onwards.
  • Return Type: String (the extracted substring).
  • Negative start value: Allows extraction from the end of the string.

Applications

  1. Text Parsing: You can extract specific parts of text data from a string, such as the domain from an email or the year from a date.
  2. Dynamic Data Extraction: You can dynamically extract parts of strings based on changing positions, such as extracting area codes from phone numbers or specific codes from identifiers.
  3. Cleaning Data: It can be useful for extracting relevant data while removing unwanted parts from longer strings.

Key Notes

  • The SUBSTRING() function is case-sensitive.
  • If length is omitted, it extracts from the start position to the end of the string.
  • If start exceeds the string length, an empty string will be returned.
Was this article helpful?