SQL SUBSTRING: 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, SQL SUBSTRING 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.

Table of Contents

• What is SUBSTRING?
• Basic SUBSTRING Examples
• Extracting with Dynamic Positions
• SUBSTRING with INSTR or CHARINDEX
• SUBSTRING vs LEFT vs RIGHT
• SUBSTRING Syntax Across Databases
• Practical Use Cases
• Common Gotchas
• Performance Considerations

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 starting at position 5.

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 by position.

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 dynamically.

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. The position-finding function differs by database:

• 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 LEFT vs RIGHT

All three functions extract parts of strings, but each has an ideal use case.

LEFT — Extract from the Start

-- MySQL, SQL Server, PostgreSQL
SELECT LEFT(product_code, 3) as prefix FROM products;

-- Equivalent with SUBSTRING
SELECT SUBSTRING(product_code, 1, 3) as prefix FROM products;

LEFT(string, n) is syntactic sugar for SUBSTRING(string, 1, n). Use LEFT when you always extract from the beginning—it’s more readable. Note that SQLite does not support LEFT; use SUBSTRING(col, 1, n) instead.

RIGHT — Extract from the End

-- MySQL, SQL Server, PostgreSQL
SELECT RIGHT(phone, 4) as last_four FROM contacts;

-- Equivalent with SUBSTRING (SQL Server style)
SELECT SUBSTRING(phone, LEN(phone) - 3, 4) as last_four FROM contacts;

RIGHT(string, n) extracts the last N characters. Again, SQLite does not support RIGHT; use SUBSTR(col, -n) instead (negative index counts from the end in SQLite).

When to Use Each

Rule of thumb: Use LEFT/RIGHT for clarity when extracting from either end. Use SUBSTRING when the start position is dynamic or in the middle of the string.

SUBSTRING Syntax Across Databases

The core concept is identical everywhere, but syntax details differ. Here’s a quick-reference table:

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;

MySQL’s DATEDIFF and date handling work differently from other databases, but string functions like SUBSTRING are consistent.

PostgreSQL

-- PostgreSQL supports SUBSTRING with standard SQL syntax too
SELECT SUBSTRING(name, 1, 5) FROM users;
SELECT SUBSTRING(name FROM 1 FOR 5) FROM users;  -- ANSI standard form

-- Postgres also supports regex extraction
SELECT SUBSTRING(email FROM '@(.*)$') as domain FROM users;

PostgreSQL’s SUBSTRING(string FROM pattern) form is the ANSI SQL standard, though the positional form works everywhere.

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;

-- LEN() instead of LENGTH()
SELECT SUBSTRING(name, LEN(name) - 4, 5) FROM users;

SQLite

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

-- Negative index in SUBSTR = count from end
SELECT SUBSTR(name, -5) FROM users;  -- last 5 characters

Oracle

Oracle uses SUBSTR (without the ING) as its primary function, though it accepts the same positional arguments:

-- Oracle
SELECT SUBSTR(name, 1, 5) FROM users;
SELECT SUBSTR(email, INSTR(email, '@') + 1) as domain FROM users;

-- REGEXP_SUBSTR for pattern-based extraction (Oracle 10g+)
SELECT REGEXP_SUBSTR(email, '[^@]+$') as domain FROM users;

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;

Extracting File Extensions

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

Data Cleaning with COALESCE

Combine SUBSTRING with COALESCE for safe handling of optional fields:

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

This returns an empty string rather than NULL when middle_name is absent—useful for display formatting.

Common Gotchas

1-Based Indexing (Not 0-Based)

SQL uses 1-based indexing. The first character is at position 1, not 0. Coming from most programming languages, this trips up almost everyone at first:

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

-- Position 0 behavior varies by database
SELECT SUBSTRING('Hello', 0, 1);
-- MySQL: Returns '' (empty)
-- SQL Server: Returns 'H' (treats 0 as 1)
-- PostgreSQL: Returns '' (empty)
-- SQLite: Returns '' (empty)

Always start with position 1 to get consistent results across databases.

NULL Propagates Through SUBSTRING

If the input string is NULL, SUBSTRING returns NULL:

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

Use COALESCE to guard against this in display contexts:

SELECT COALESCE(SUBSTRING(nickname, 1, 10), 'N/A') as display_name
FROM users;

When start_position Exceeds String Length

What happens when you ask for a position past the end of the string?

-- String is 5 chars, start at position 10
SELECT SUBSTRING('Hello', 10, 5);
-- MySQL: Returns ''
-- PostgreSQL: Returns ''
-- SQL Server: Returns ''
-- SQLite: Returns ''

All major databases return an empty string (not an error). But be aware that your results will silently be empty rather than raising a flag.

When length Extends Past the String

What if the string is shorter than your requested length?

SELECT SUBSTRING('Bob', 1, 10);  -- Returns: 'Bob'

SUBSTRING gracefully returns whatever is available—it won’t pad with spaces or raise an error.

Empty String Behavior

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

This is consistent across databases but worth knowing—SUBSTRING on an empty string returns an empty string, not NULL.

Performance Considerations

Indexing and SUBSTRING

SUBSTRING in WHERE clauses prevents index usage on the column:

-- 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%';

LIKE 'prefix%' (prefix matching) can use a B-tree index. SUBSTRING in a WHERE clause can’t, because it requires evaluating every row.

When SUBSTRING is Unavoidable

If you frequently filter by extracted portions, consider:

1. Adding a computed/generated column that stores the extracted value
2. Creating a functional index (PostgreSQL, Oracle support this)
3. Denormalizing the data into separate columns at write time

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

-- Now this can use the index
SELECT * FROM products
WHERE SUBSTRING(product_code, 1, 3) = 'ELE';

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 across all the databases covered in this guide.

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, Oracle, 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, not 0)
• Omitting length returns everything from the start position to the end
• Combine with INSTR/CHARINDEX/POSITION to find dynamic positions
• Use LEFT/RIGHT for simpler beginning/end extractions—but note SQLite doesn’t support them
• NULL propagates: if the input is NULL, SUBSTRING returns NULL; guard with COALESCE
• SUBSTRING in WHERE clauses prevents index use—prefer LIKE for prefix matching
• Oracle uses SUBSTR, not SUBSTRING, as its primary function name
• PostgreSQL supports the ANSI SUBSTRING(str FROM pos FOR len) form

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

Quick reference: Bookmark the SQL Cheat Sheet for common string function syntax, and see the SQL COALESCE guide for handling NULL values alongside string extraction.

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