How to Use SUBSTRING in SQL

Use SUBSTRING() when you need to extract only part of a text value instead of the full string. This is useful for prefixes, country codes, masked IDs, partial labels, and parsing structured text.

What you’ll build or solve

You’ll learn how to use SUBSTRING in SQL to extract text by position and length. You’ll also know how indexing rules can differ across databases.

When this approach works best

This approach is the right choice when the text structure is predictable by character position.

Common real-world scenarios include:

• Extracting area codes
• Masking last 4 digits
• Parsing SKU prefixes
• Reading year prefixes
• Short preview labels

This is a bad idea when delimiters matter more than fixed positions. In that case, use split or regex helpers if your database supports them.

Prerequisites

You only need:

• A SQL table with text columns
• Basic SELECT knowledge

Step-by-step instructions

Step 1: Pass the text, start position, and length

The common pattern is:

SUBSTRING(column, start, length)

Example:

SELECT SUBSTRING(phone_number, 1, 3) AS area_code
FROM customers;

This extracts the first 3 characters.

Use it for suffix masking too.

SELECT SUBSTRING(card_number, 13, 4) AS last_four
FROM payments;

This keeps only the final 4 digits when the format is fixed.

What to look for:

• Start position is often 1-based
• Length controls extracted size
• Great for fixed-format strings
• Useful for prefixes and masking
• Database syntax can vary slightly

Examples you can copy

SKU prefix

SELECT SUBSTRING(sku, 1, 3) AS prefix
FROM products;

Last 4 digits

SELECT SUBSTRING(card_number, 13, 4) AS last_four
FROM cards;

Year prefix

SELECT SUBSTRING(order_code, 1, 4) AS year
FROM orders;

Common mistakes and how to fix them

Mistake 1: Using zero-based positions

What the reader might do:

SUBSTRING(phone_number, 0, 3)

Why it breaks: many SQL databases start counting at 1.

Corrected approach:

SUBSTRING(phone_number, 1, 3)

Mistake 2: Wrong fixed positions for variable text

What the reader might do:

SUBSTRING(email, 1, 5)

Why it breaks: varying lengths make the extracted meaning inconsistent.

Corrected approach:

Use delimiter-based functions for variable formats.

Mistake 3: Extracting past the string length

What the reader might do:

SUBSTRING(code, 20, 5)

Why it breaks: short values may return empty strings.

Corrected approach:

Validate the format or use LENGTH() checks.

Troubleshooting

If the result is off by one, confirm the database uses 1-based indexing.

If the output is empty, check string length assumptions.

If values vary in structure, switch to delimiter-based parsing.

If portability matters, verify syntax differences (SUBSTR vs SUBSTRING).

Quick recap

• Use SUBSTRING() for fixed-position text extraction
• Most databases start at position 1
• Use length to control output size
• Great for prefixes and masking
• Use delimiter parsing for variable formats