SUBSTRING and LEFT/RIGHT in MySQL - Deep Dive

Overview - SUBSTRING and LEFT/RIGHT

What is it?

SUBSTRING, LEFT, and RIGHT are functions in MySQL used to extract parts of text from a larger string. SUBSTRING lets you pick a piece starting at any position for a certain length. LEFT and RIGHT extract characters from the start or end of the string, respectively. These functions help you work with text data by breaking it into smaller, meaningful parts.

Why it matters

Without these functions, it would be hard to get specific parts of text stored in databases, like extracting area codes from phone numbers or first names from full names. They save time and avoid errors compared to manual cutting or guessing. This makes data handling more efficient and accurate in real applications like reports, searches, and data cleaning.

Where it fits

Before learning these, you should understand basic SQL queries and string data types. After mastering these functions, you can explore more advanced string functions like REPLACE, CONCAT, and REGEXP for pattern matching and text manipulation.

Mental Model

Core Idea

These functions let you cut out exactly the part of a text string you want by specifying where to start and how many characters to take.

Think of it like...

Imagine a long ribbon with letters printed on it. SUBSTRING is like cutting a piece from anywhere along the ribbon, LEFT is cutting from the start, and RIGHT is cutting from the end.

String:  H  e  l  l  o  W  o  r  l  d
Index:   1  2  3  4  5  6  7  8  9  10

SUBSTRING(str, 3, 4) -> 'lloW'
LEFT(str, 5) -> 'Hello'
RIGHT(str, 5) -> 'World'

Build-Up - 7 Steps

1

Foundation

Understanding Basic String Extraction

Concept: Learn what it means to extract part of a string by position and length.

Strings are sequences of characters. Extracting means taking a smaller piece from this sequence. For example, from 'HelloWorld', taking characters 1 to 5 gives 'Hello'.

Result

You understand that strings can be sliced by specifying positions.

Understanding strings as sequences helps you see why you can pick parts by counting characters.

2

Foundation

Using LEFT and RIGHT Functions

Concept: LEFT and RIGHT extract characters from the start or end of a string.

LEFT('HelloWorld', 5) returns 'Hello' because it takes the first 5 characters. RIGHT('HelloWorld', 5) returns 'World' because it takes the last 5 characters.

Result

You can quickly get the beginning or end of any text without counting positions manually.

Knowing LEFT and RIGHT simplifies common tasks like getting prefixes or suffixes from text.

3

Intermediate

Using SUBSTRING for Flexible Extraction

🤔Before reading on: do you think SUBSTRING can only extract from the start or can it extract from anywhere? Commit to your answer.

Concept: SUBSTRING lets you extract text starting at any position for a given length.

SUBSTRING('HelloWorld', 3, 4) returns 'lloW' because it starts at position 3 and takes 4 characters. Positions start at 1, so position 3 is 'l'.

Result

You can extract any part of a string, not just from the start or end.

Understanding SUBSTRING's flexibility lets you handle complex text extraction tasks.

4

Intermediate

Handling Edge Cases in Extraction

🤔Before reading on: what happens if you ask SUBSTRING for more characters than remain in the string? Predict the result.

Concept: Learn how these functions behave when asked for more characters than available or invalid positions.

If SUBSTRING('Hello', 4, 10) is called, it returns 'lo' because only 2 characters remain from position 4. If position is beyond string length, it returns an empty string.

Result

You know how to avoid errors and unexpected empty results when extracting.

Knowing function limits prevents bugs when working with variable-length text.

5

Intermediate

Using Negative Positions in SUBSTRING

🤔Before reading on: do you think SUBSTRING supports negative start positions? Guess what happens if you use one.

Concept: SUBSTRING in MySQL can use negative start positions to count from the string's end.

SUBSTRING('HelloWorld', -5, 3) returns 'Wor' because it starts 5 characters from the end and takes 3 characters.

Result

You can extract text relative to the end without calculating string length.

Negative indexing adds powerful flexibility for text extraction.

6

Advanced

Combining SUBSTRING with Other Functions

🤔Before reading on: can you combine SUBSTRING with LENGTH or LOCATE to extract dynamic parts? Predict how.

Concept: Use SUBSTRING with functions like LENGTH and LOCATE to extract parts based on content, not fixed positions.

Example: To get the domain from '[e-mail]', use SUBSTRING(email, LOCATE('@', email) + 1). LOCATE finds '@', SUBSTRING extracts after it.

Result

You can extract meaningful parts of text dynamically, like usernames or file extensions.

Combining functions lets you handle real-world text patterns flexibly.

7

Expert

Performance and Indexing Considerations

🤔Before reading on: do you think using SUBSTRING in WHERE clauses affects query speed? Guess why or why not.

Concept: Using SUBSTRING in queries can prevent MySQL from using indexes, slowing down searches.

When you write WHERE SUBSTRING(column, 1, 3) = 'abc', MySQL must process every row's substring, ignoring indexes. This can cause slow queries on large tables.

Result

You understand when to avoid or optimize substring usage for performance.

Knowing how functions affect indexing helps write efficient database queries.

Under the Hood

MySQL stores strings as sequences of characters with positions starting at 1. SUBSTRING and LEFT/RIGHT calculate offsets and lengths to return new strings. Internally, these functions copy the requested characters into a new memory space for output. Negative positions in SUBSTRING are translated to positive offsets from the string's end before extraction.

Why designed this way?

These functions were designed to be simple and flexible for common text operations. LEFT and RIGHT provide quick access to string ends, a frequent need. SUBSTRING offers full control for any part extraction. Negative indexing was added later to simplify end-relative operations, avoiding manual length calculations.

Input String: [H][e][l][l][o][W][o][r][l][d]
Positions:    1  2  3  4  5  6  7  8  9  10

SUBSTRING(str, 3, 4): Extract from pos 3 length 4 -> [l][l][o][W]
LEFT(str, 5): Extract first 5 chars -> [H][e][l][l][o]
RIGHT(str, 5): Extract last 5 chars -> [W][o][r][l][d]

Myth Busters - 4 Common Misconceptions

Quick: Does LEFT(str, n) return the first n characters or the last n characters? Commit to your answer.

Common Belief: LEFT(str, n) returns the last n characters of the string.

Reality: LEFT(str, n) returns the first n characters from the start of the string.

Why it matters: Confusing LEFT and RIGHT leads to wrong data extraction, causing bugs in reports or data processing.

Quick: If SUBSTRING is called with a start position beyond string length, does it return an error or empty string? Commit now.

Common Belief: SUBSTRING returns an error if the start position is beyond the string length.

Reality: SUBSTRING returns an empty string if the start position is beyond the string length.

Why it matters: Expecting errors can cause unnecessary error handling; knowing it returns empty helps write cleaner code.

Quick: Does SUBSTRING support negative start positions in all SQL databases? Commit your guess.

Common Belief: Negative start positions in SUBSTRING work the same in all SQL databases.

Reality: Negative start positions are supported in MySQL but not in all SQL databases; behavior varies.

Why it matters: Assuming portability can cause bugs when moving queries between databases.

Quick: Does using SUBSTRING in WHERE clauses always use indexes? Commit your answer.

Common Belief: Using SUBSTRING in WHERE clauses does not affect index usage.

Reality: Using SUBSTRING on indexed columns in WHERE clauses usually prevents index usage, slowing queries.

Why it matters: Ignoring this leads to slow database performance on large datasets.

Expert Zone

1

SUBSTRING with negative start positions counts from the end but length is always positive, which can confuse extraction length calculations.

2

LEFT and RIGHT are essentially shortcuts for SUBSTRING with fixed start positions, but using SUBSTRING can be clearer in complex expressions.

3

Using SUBSTRING in JOIN or WHERE clauses can cause full table scans; rewriting queries or adding computed columns can improve performance.

When NOT to use

Avoid using SUBSTRING or LEFT/RIGHT in WHERE clauses on large tables if performance is critical. Instead, use indexed computed columns or full-text search. For complex pattern extraction, consider REGEXP or application-level processing.

Production Patterns

In production, SUBSTRING is often combined with LOCATE or INSTR to extract dynamic parts like domains or file extensions. LEFT and RIGHT are used for fixed-length prefixes or suffixes like country codes. Performance-aware developers avoid using these functions in filters on large datasets without indexes.

Connections

Regular Expressions

SUBSTRING and LEFT/RIGHT provide simple fixed-position extraction, while regular expressions allow pattern-based extraction.

Understanding fixed-position extraction helps grasp why regex is more powerful but also more complex and slower.

Array Slicing in Programming

Both involve extracting a portion of a sequence by specifying start and length.

Knowing array slicing in programming languages makes it easier to understand string extraction functions in SQL.

Text Editing

Cutting parts of strings in SQL is like selecting and copying parts of text in an editor.

This connection helps realize that string functions automate manual text editing tasks inside databases.

Common Pitfalls

#1 Extracting more characters than the string length without checking.

Wrong approach:

SELECT SUBSTRING('Hello', 4, 10);

Correct approach:

SELECT SUBSTRING('Hello', 4, 2);

Root cause:

Not understanding that SUBSTRING returns only available characters and that length should be realistic.

#2 Using SUBSTRING in WHERE clause causing slow queries.

Wrong approach:

SELECT * FROM users WHERE SUBSTRING(phone, 1, 3) = '123';

Correct approach:

SELECT * FROM users WHERE phone LIKE '123%';

Root cause:

Not realizing that functions on columns disable index use, while LIKE with prefix can use indexes.

#3 Confusing LEFT and RIGHT functions.

Wrong approach:

SELECT RIGHT('HelloWorld', 5); -- expecting 'Hello'

Correct approach:

SELECT LEFT('HelloWorld', 5); -- returns 'Hello'

Root cause:

Misunderstanding that LEFT extracts from start and RIGHT from end.

Key Takeaways

SUBSTRING, LEFT, and RIGHT are essential MySQL functions to extract parts of text by position and length.

LEFT and RIGHT quickly get characters from the start or end, while SUBSTRING can extract from anywhere in the string.

Negative start positions in SUBSTRING count from the end, adding flexible extraction options.

Using these functions in WHERE clauses can hurt performance by disabling indexes; use alternatives like LIKE when possible.

Combining SUBSTRING with functions like LOCATE enables dynamic and meaningful text extraction in real-world data.