SUBSTRING & MID
In this page:
Basic Substring
SUBSTRING extracts a smaller piece of text out of a larger string by position, similar to cropping a photo down to just the part you need. MySQL counts string positions starting at 1, not 0, which trips up developers coming from languages like Python or JavaScript.
Example: Basic Substring
SELECT SUBSTRING('Hello World', 1, 5) AS greeting;Using the MID Function
MID is simply an alias for SUBSTRING with identical behavior and argument order. MySQL keeps it around mainly for compatibility with other SQL dialects and older scripts that were written using that name, so you can use whichever reads more naturally to you.
Example: Using the MID Function
SELECT MID('Hello World', 7, 5) AS target_word;Negative Indexing
Passing a negative starting position tells MySQL to count backward from the end of the string instead of the beginning. This is a quick way to grab the last few characters of a value, such as pulling the last 4 digits of a card number without calculating the string's length first.
Example: Negative Indexing
CREATE TABLE cards (id INT, card_number TEXT);
INSERT INTO cards VALUES (1, '4111111111111234');
SELECT SUBSTRING(card_number, -4) AS last_four FROM cards;Setting a Length Limit
Adding a third argument caps how many characters SUBSTRING returns, so it stops instead of reading all the way to the end of the string. Combine a start position with a length to slice out an exact fixed-width chunk, like a 2-character country code.
Example: Setting a Length Limit
CREATE TABLE countries (id INT, code_and_name TEXT);
INSERT INTO countries VALUES (1, 'INIndia');
SELECT SUBSTRING(code_and_name, 1, 2) AS country_code FROM countries;Practical Use Cases
Real queries often chain SUBSTRING with other string functions to pull structured pieces out of unstructured text, such as isolating a year from a filename or a domain from an email address stored as a single column.
Example: Practical Use Cases
CREATE TABLE emails (id INT, email TEXT);
INSERT INTO emails VALUES (1, '[email protected]');
SELECT SUBSTRING(email, LOCATE('@', email) + 1) AS domain FROM emails;Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: