MySQL SUBSTRING() Function
The SUBSTRING() function in MySQL extracts a part (substring) of a string, starting at a specified position and continuing for a given length. It's a very useful function for manipulating and working with text data.
SUBSTRING(): Definition and Usage
SUBSTRING() allows you to select a specific portion of a string. You specify where to start and how many characters to include. MySQL's SUBSTRING() function is equivalent to both SUBSTR() and MID().
Understanding Starting Positions
The starting position is 1-based, meaning the first character is at position 1. You can also use negative numbers; -1 refers to the last character, -2 is the second-to-last, and so on.
| Positive Position | Character | Negative Position | Character |
|---|---|---|---|
| 1 | S | -12 | S |
| 2 | Q | -11 | Q |
| 3 | L | -10 | L |
| 4 | -9 | ||
| 5 | T | -8 | T |
| 6 | u | -7 | u |
| 7 | t | -6 | t |
| 8 | o | -5 | o |
| 9 | r | -4 | r |
| 10 | i | -3 | i |
| 11 | a | -2 | a |
| 12 | l | -1 | l |
Syntax
Syntax
SUBSTRING(string, start, length)
--or--
SUBSTRING(string FROM start FOR length)
Parameter Values
| Parameter | Description |
|---|---|
string |
The string to extract from. This is required. |
start |
The starting position (can be positive or negative). This is required. |
length (Optional) |
The number of characters to extract. If omitted, it extracts to the end of the string. |
Examples
Extracting a Substring
This extracts three characters starting from position 1 ("SQL").
Syntax
SELECT SUBSTRING("SQL Tutorial", 1, 3) AS ExtractString;
Output
SQL
Extracting from a Column
Extracts five characters from the 'CustomerName' column starting from the second position (assuming a 'Customers' table exists with a 'CustomerName' column).
Syntax
SELECT SUBSTRING(CustomerName, 2, 5) AS ExtractString FROM Customers;
Output
ExtractString
-------------
(The extracted substring from each CustomerName will be displayed here.)
Extracting from the End of a String
Extracting five characters from the end of the string.
Syntax
SELECT SUBSTRING("SQL Tutorial", -5, 5) AS ExtractString;
Output
orial