MySQL SUBSTR() Function

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

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

Syntax

SUBSTR(string, start, length)

Or:

SUBSTR(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 SUBSTR() function extracts a part of the string starting from a given start position.
  • If the start value is positive, the extraction starts from the left of the string.
  • If the start value is negative, the extraction starts from the end of the string.
  • If length is specified, it defines the number of characters to extract from the start position. If omitted, the substring extends to the end of the string.

Return Values

  • The function returns a substring based on the specified start and length values.
  • If the start position exceeds the string length, the function will return an empty string.

Usage Examples

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

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

Output:

ExtractString
Tut

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

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

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

Output: (Based on the data in CustomerName)

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

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

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

Output:

ExtractString
orial

Explanation: Starting from the 5th position from the end (the "o" in "orial"), the function 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. Extracting Substrings: Use SUBSTR() to extract a portion of text from a column, which can be useful for text manipulation, parsing data, or formatting results.
  2. Working with Dynamic Data: You can extract specific parts of strings dynamically based on the position of characters, such as extracting area codes from phone numbers or specific identifiers from codes.
  3. Cleaning and Formatting: It can be used to clean up text data by extracting relevant parts or removing unnecessary data from longer strings.

Key Notes

  • The SUBSTR() function is case-sensitive.
  • If length is omitted, the function will extract from the start position until the end of the string.
  • The function will return an empty string if the start position is greater than the length of the string.
Was this article helpful?