SQL SUBSTRING Function: Complete Guide with Interactive Examples

Working with text data is one of the most common tasks in SQL. Whether you need to extract product codes, parse phone numbers, or clean up messy data, the SQL SUBSTRING function is your go-to tool for pulling out exactly the characters you need.

The SUBSTRING function lets you extract a portion of a string based on position and length. It’s supported across all major databases and is essential for text manipulation, data cleaning, and report formatting.

Throughout this guide, you’ll find interactive SQL exercises that let you practice SUBSTRING in your browser—no database setup required. Write queries, get instant feedback, and build real skills with text extraction.

What is SUBSTRING?

SUBSTRING extracts a portion of a string starting from a specified position. The basic syntax is:

SUBSTRING(string, start_position, length)

Key points:

• string: The text column or value to extract from
• start_position: Where to start (1-based in SQL, meaning the first character is position 1)
• length: How many characters to extract (optional in some databases)

If you omit the length parameter, SUBSTRING returns everything from the start position to the end of the string.

Basic SUBSTRING Examples

Let’s start with simple extraction scenarios.

Extracting from the Beginning

The most common use case is extracting the first N characters:

SELECT product_code, SUBSTRING(product_code, 1, 3) as category_prefix
FROM products;

This extracts the first 3 characters from each product code—useful when codes have embedded meaning like “ELE-1234” where “ELE” indicates Electronics.

Extracting from a Specific Position

You can start from any position in the string:

SELECT order_number, SUBSTRING(order_number, 5, 4) as year
FROM orders;

If order numbers are formatted like “ORD-2024-001”, this extracts the year portion.

Try it yourself:

[[ error ]]

[[ testData.solution ]]

Extracting with Dynamic Positions

SUBSTRING becomes powerful when combined with other string functions to find positions dynamically.

Working with Delimited Data

Many real-world scenarios involve extracting parts of formatted strings. Consider phone numbers:

SELECT
  phone_number,
  SUBSTRING(phone_number, 2, 3) as area_code,
  SUBSTRING(phone_number, 7, 3) as exchange,
  SUBSTRING(phone_number, 11, 4) as subscriber
FROM contacts;

For a phone number like “(415) 555-1234”, this extracts each component.

Try it yourself:

[[ error ]]

[[ testData.solution ]]

SUBSTRING with INSTR or CHARINDEX

The real power comes when you combine SUBSTRING with functions that find character positions.

Finding the @ in Email Addresses

To extract the domain from an email:

SELECT
  email,
  SUBSTRING(email, INSTR(email, '@') + 1) as domain
FROM users;

This finds where the @ symbol is located, then extracts everything after it. Different databases use different function names:

• SQLite/MySQL: INSTR(string, substring)
• PostgreSQL: POSITION(substring IN string)
• SQL Server: CHARINDEX(substring, string)

Extracting Text Between Delimiters

Imagine extracting the middle name from “First Middle Last”:

SELECT
  full_name,
  SUBSTRING(
    full_name,
    INSTR(full_name, ' ') + 1,
    INSTR(SUBSTRING(full_name, INSTR(full_name, ' ') + 1), ' ') - 1
  ) as middle_name
FROM employees;

This is complex—often it’s cleaner to handle such parsing in your application code, but SUBSTRING can do it when needed.

Try it yourself:

[[ error ]]

[[ testData.solution ]]

SUBSTRING vs SUBSTR vs LEFT/RIGHT

Different databases offer variations:

SUBSTR (Oracle, SQLite alternative)

-- Oracle and SQLite also support SUBSTR
SELECT SUBSTR(name, 1, 5) FROM users;

LEFT and RIGHT Functions

For simple beginning/end extraction, some databases offer cleaner alternatives:

-- MySQL, SQL Server, PostgreSQL
SELECT LEFT(product_code, 3) as prefix FROM products;
SELECT RIGHT(phone, 4) as last_four FROM contacts;

Choosing the Right Function

• Use LEFT/RIGHT when extracting from the start or end (cleaner syntax)
• Use SUBSTRING when starting from a middle position or using dynamic positions
• Use SUBSTR in Oracle or when you prefer the shorter name

Practical Use Cases

Masking Sensitive Data

Show only the last 4 digits of credit cards:

SELECT
  customer_name,
  '****-****-****-' || SUBSTRING(card_number, 13, 4) as masked_card
FROM payments;

Parsing Fixed-Width Data

Legacy systems often use fixed-width fields:

-- Record: "JOHNDOE19850315NYC"
SELECT
  SUBSTRING(record, 1, 8) as name,
  SUBSTRING(record, 9, 8) as birth_date,
  SUBSTRING(record, 17, 3) as city_code
FROM legacy_data;

Creating URL Slugs

Extract a cleaner URL from page titles:

SELECT
  title,
  LOWER(SUBSTRING(REPLACE(title, ' ', '-'), 1, 50)) as url_slug
FROM articles;

Extracting File Extensions

SELECT
  filename,
  SUBSTRING(filename, INSTR(filename, '.') + 1) as extension
FROM documents
WHERE INSTR(filename, '.') > 0;

Handling Edge Cases

Strings Shorter Than Expected

What happens when the string is shorter than your extraction length?

-- If name is "Bob" and you ask for SUBSTRING(name, 1, 10)
-- Result: "Bob" (not an error, just returns what's available)
SELECT SUBSTRING('Bob', 1, 10);  -- Returns: "Bob"

SUBSTRING gracefully handles this by returning the available characters.

Empty Strings and NULLs

-- Empty string
SELECT SUBSTRING('', 1, 5);  -- Returns: ''

-- NULL handling
SELECT SUBSTRING(NULL, 1, 5);  -- Returns: NULL

Combine with COALESCE for safer handling:

SELECT COALESCE(SUBSTRING(middle_name, 1, 1), '') as middle_initial
FROM employees;

Start Position Beyond String Length

-- Start position past the string length
SELECT SUBSTRING('Hello', 10, 5);  -- Returns: '' (empty string)

Performance Considerations

Indexing and SUBSTRING

SUBSTRING in WHERE clauses can prevent index usage:

-- This won't use an index on product_code efficiently
SELECT * FROM products
WHERE SUBSTRING(product_code, 1, 3) = 'ELE';

-- Better: Use LIKE for prefix matching (can use index)
SELECT * FROM products
WHERE product_code LIKE 'ELE%';

When SUBSTRING is Unavoidable

If you frequently filter by extracted portions, consider:

1. Adding a computed/generated column
2. Creating a functional index (PostgreSQL, Oracle)
3. Denormalizing the data into separate columns

-- PostgreSQL functional index
CREATE INDEX idx_products_prefix ON products (SUBSTRING(product_code, 1, 3));

Database-Specific Syntax

MySQL

-- MySQL supports multiple syntaxes
SELECT SUBSTRING(name, 1, 5) FROM users;
SELECT SUBSTR(name, 1, 5) FROM users;
SELECT MID(name, 1, 5) FROM users;  -- MySQL-specific alias
SELECT LEFT(name, 5) FROM users;

PostgreSQL

-- PostgreSQL supports SUBSTRING with pattern matching
SELECT SUBSTRING(email FROM '@(.*)$') as domain FROM users;
SELECT SUBSTRING(name, 1, 5) FROM users;

SQL Server

-- SQL Server syntax
SELECT SUBSTRING(name, 1, 5) FROM users;
SELECT LEFT(name, 5) FROM users;
-- Use CHARINDEX instead of INSTR
SELECT SUBSTRING(email, CHARINDEX('@', email) + 1, 100) as domain FROM users;

SQLite

-- SQLite supports both SUBSTRING and SUBSTR
SELECT SUBSTRING(name, 1, 5) FROM users;
SELECT SUBSTR(name, 1, 5) FROM users;

Common Pitfalls

Zero-Based vs One-Based Indexing

SQL uses 1-based indexing. The first character is at position 1, not 0:

-- Correct: First character is position 1
SELECT SUBSTRING('Hello', 1, 1);  -- Returns: 'H'

-- Wrong assumption: Position 0
SELECT SUBSTRING('Hello', 0, 1);  -- Behavior varies by database

Negative Positions

Some databases support negative positions (count from end), others don’t:

-- PostgreSQL: negative start counts from end
SELECT SUBSTRING('Hello' FROM -2);  -- Returns: 'lo'

-- MySQL/SQL Server: negative positions don't work the same way

Forgetting Length Parameter

Without a length, SUBSTRING returns everything from the start position:

SELECT SUBSTRING('Hello World', 7);  -- Returns: 'World'

This is useful but can surprise you if you expected just one character.

Working with SUBSTRING in Beekeeper Studio

When you’re working with string manipulation, having a SQL editor that helps you visualize results makes a big difference. Beekeeper Studio provides excellent support for testing SUBSTRING queries.

Features that help when working with SUBSTRING:

• Query autocompletion: Suggests string functions as you type
• Instant results: See extracted text immediately in the result grid
• Query history: Save and reuse your text parsing patterns
• Multi-database support: Use SUBSTRING across PostgreSQL, MySQL, SQLite, SQL Server, and more
• Data export: Export your parsed results to CSV or other formats

The free version includes everything you need to master SQL string functions.

Key Takeaways

The SQL SUBSTRING function is essential for working with text data. Here’s what to remember:

• SUBSTRING extracts characters by position and length from strings
• Positions are 1-based in SQL (first character is position 1)
• Omitting length returns everything from the start position to the end
• Combine with INSTR/CHARINDEX to find dynamic positions
• Use LEFT/RIGHT for simpler beginning/end extractions
• Be careful with indexes - SUBSTRING in WHERE can hurt performance
• Handle edge cases - short strings and NULLs won’t cause errors
• Syntax varies slightly across databases, but the concept is the same

By mastering SUBSTRING, you’ll be able to parse, clean, and transform text data effectively. Whether you’re extracting codes from product IDs, parsing log files, or formatting display data, SUBSTRING is a tool you’ll use regularly.

Ready to practice more? Try the interactive examples above, or explore more SQL tutorials to continue building your database skills.