MySQL MID() Function

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

The MID() function in MySQL is used to extract a substring from a given string, starting at a specified position and with a specified length.

Syntax

MID(string, start, length)

Parameters

Parameter Description
string Required. The string to extract the substring from.
start Required. The starting position for the extraction. Can be both a positive or negative number. If it's positive, it starts from the beginning of the string; if negative, it starts from the end.
length Required. The number of characters to extract from the string, starting from the specified position.

Definition and Use

  • Purpose: The MID() function extracts a substring from the string based on the starting position and the length specified.
    • If the start is positive, the function begins from that position, counting from the left (1-based index).
    • If the start is negative, the function begins from that position, counting from the right.
    • If length is omitted, it extracts the substring from the start position to the end of the string.
  • Note: MID() is equivalent to the SUBSTR() function, and both can also be used interchangeably with the SUBSTRING() function.

Usage Examples

Example 1: Basic Usage

Extract a substring starting at position 5 and extract 3 characters from "SQL Tutorial":

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

Output:

ExtractString
Tuto

Explanation: Starting from the 5th position, 3 characters are extracted, resulting in the substring "Tuto".

Example 2: Extracting from a Column in a Table

Extract a substring from the CustomerName column, starting at position 2 and extracting 5 characters:

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

Output (example data):

ExtractString
ohn Sm
lice Bro

Explanation: Starting from the 2nd position, 5 characters are extracted from the CustomerName field.

Example 3: Using Negative Start Position

Extract a substring from the string "SQL Tutorial", starting from position -5 (5 characters from the end) and extracting 5 characters:

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

Output:

ExtractString
orial

Explanation: Starting from the 5th position from the end of the string, 5 characters are extracted, resulting in "orial".

Technical Details

  • Works In: From MySQL 4.0 onwards.
  • Return Type: Returns a substring from the original string based on the given position and length.
  • String Manipulation: The function allows flexible extraction, either starting from the beginning of the string (positive index) or from the end (negative index).

Applications

  1. Substring Extraction: Useful when working with long strings or large data fields, especially when you need to extract portions of text (e.g., part of an address, product name, or code).
  2. Data Parsing: Helps in parsing and analyzing strings, like extracting certain characters from codes or descriptions.
  3. Dynamic Querying: Can be used in queries to extract specific parts of a field dynamically based on user input or conditions.

Key Notes

  • The start index is 1-based. For example, the first character of the string is at position 1.
  • If the start position is larger than the length of the string, or if the length exceeds the remaining characters from the start, the function will return the substring from the start position to the end of the string.
  • Negative start values count from the end of the string, allowing you to easily extract parts of the string from the back.

The MID() function is highly useful for extracting portions of strings in MySQL and is often used in scenarios like data cleaning, substring extraction, and dynamic report generation.

Was this article helpful?