SQL SUBSTRING Guide

May 4, 2026 By Matthew Rathbone

SUBSTRING returns part of a string, starting at the position you give it and running for as many characters as you ask for. It’s how SQL pulls a prefix, a code or an email domain out of a text column. The examples work in PostgreSQL, MySQL, SQLite and SQL Server.

How to use SUBSTRING

Give SUBSTRING a string, the position to start at (the first character is 1) and how many characters to take. Leave out the length to take everything up to the end:

SUBSTRING(string, start, length)

SELECT SUBSTRING('Joe Bloggs', 1, 3);
-- Output: Joe

-- No length: the rest of the string (SQL Server needs all three arguments)
SELECT SUBSTRING('Joe Bloggs', 5);
-- Output: Bloggs

In a real query the string is usually a column. Order numbers like ORD-2024-001 keep the year in characters 5 to 8:

-- Input: orders
-- order_number
-- ------------
-- ORD-2024-001
-- ORD-2025-014
-- ORD-2026-102

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

-- Output:
-- order_number | year
-- -------------+-----
-- ORD-2024-001 | 2024
-- ORD-2025-014 | 2025
-- ORD-2026-102 | 2026

Rules to know

Positions start at 1, not 0. A negative start counts back from the end of the string, but only in MySQL and SQLite:

SELECT SUBSTRING('Joe Bloggs', -6, 6);
-- Output: Bloggs
-- PostgreSQL and SQL Server return '' (an empty string) instead

Running past the end isn’t an error. You get the characters that exist, or an empty string when the start is past the end:

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

SELECT SUBSTRING('Hello', 10, 5);
-- Output: '' (an empty string)

NULL in, NULL out. Wrap the call in COALESCE when you need a value instead:

SELECT SUBSTRING(NULL, 1, 5);
-- Output: NULL
-- SQL Server rejects a bare NULL here, but a NULL column gives NULL

Filter on a prefix with LIKE, not SUBSTRING. WHERE SUBSTRING(product_code, 1, 3) = 'ELE' returns the same rows as WHERE product_code LIKE 'ELE%', but only the LIKE version can use an index on product_code.

Alternative syntax

You’ll also see SUBSTR, another name for SUBSTRING, and LEFT and RIGHT, which take characters from either end:

-- Not in SQL Server
SELECT SUBSTR('Joe Bloggs', 1, 3);
-- Output: Joe

-- Not in SQLite: use SUBSTRING('Joe Bloggs', 1, 3) and SUBSTRING('Joe Bloggs', -6)
SELECT LEFT('Joe Bloggs', 3);
-- Output: Joe

SELECT RIGHT('Joe Bloggs', 6);
-- Output: Bloggs

-- PostgreSQL and MySQL: the standard SQL form
SELECT SUBSTRING('Joe Bloggs' FROM 1 FOR 3);
-- Output: Joe

To start after a particular character, find its position first:

SELECT INSTR('joe@example.com', '@');
-- Output: 4
-- PostgreSQL: POSITION('@' IN 'joe@example.com'); SQL Server: CHARINDEX('@', 'joe@example.com')

When should you use SUBSTRING?

Reach for SUBSTRING when the part of a string you need sits at a known position, or just after a character you can find. Most uses fall into one of these four.

Take the first few characters

Codes and names often carry meaning at the start: the category in a product code, an initial, a short preview of a long description. SUBSTRING(column, 1, n) takes the first n characters.

Pull fields out of fixed-format values

When every value has the same layout, each part sits at the same position in every row: the area code in a phone number like (303) 555-0147, the year in ORD-2024-001, or the fields of a fixed-width record from an older system.

Extract text after a delimiter

Email domains, file extensions and URL paths start after a character whose position changes from row to row. Find its position with INSTR, start one character later, and take the rest of the string.

Keep the last few characters

Masking a card or account number, or reading a suffix, means counting back from the end. Work out the start from the string’s length, or, in MySQL and SQLite, use a negative start.

Real examples of SUBSTRING

Count products by the category in their code

Product codes start with a three-letter category. Group by that prefix to count the products in each:

-- Input: products
-- product_code | name
-- -------------+-----------
-- ELE-1234     | USB-C Hub
-- FUR-0087     | Desk Chair
-- ELE-5521     | Webcam
-- OFF-2210     | Stapler

SELECT SUBSTRING(product_code, 1, 3) AS category, COUNT(*) AS products
FROM products
GROUP BY SUBSTRING(product_code, 1, 3)
ORDER BY category;

-- Output:
-- category | products
-- ---------+---------
-- ELE      | 2
-- FUR      | 1
-- OFF      | 1

Parse a fixed-width record

Exports from older systems often pack fields into fixed positions with no delimiter. Here the name fills 8 characters (padded with spaces, which TRIM removes), the birth date the next 8 and the city code the last 3:

-- Input: legacy_data
-- record
-- -------------------
-- JOHNDOE 19850315NYC
-- MARYLEE 19780912CHI
-- JANEROE 19900722LAX

SELECT
  TRIM(SUBSTRING(record, 1, 8)) AS name,
  SUBSTRING(record, 9, 8) AS birth_date,
  SUBSTRING(record, 17, 3) AS city_code
FROM legacy_data;

-- Output:
-- name    | birth_date | city_code
-- --------+------------+----------
-- JOHNDOE | 19850315   | NYC
-- MARYLEE | 19780912   | CHI
-- JANEROE | 19900722   | LAX

Get each file’s extension

Find the dot, then take everything after it. The WHERE clause skips files without one:

-- Input: documents
-- filename
-- ---------------
-- q3-report.pdf
-- budget.xlsx
-- README
-- team-photo.jpeg

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

-- Output:
-- filename        | extension
-- ----------------+----------
-- q3-report.pdf   | pdf
-- budget.xlsx     | xlsx
-- team-photo.jpeg | jpeg

In PostgreSQL, use POSITION('.' IN filename) instead of INSTR. In SQL Server, use CHARINDEX('.', filename) and add a length, such as LEN(filename).

Mask a card number

Card numbers aren’t all 16 digits, so a fixed start position cuts American Express numbers short. Count back from the end instead:

-- Input: payments
-- customer_name | card_number
-- --------------+-----------------
-- Lena Fischer  | 4111111111111111
-- Marcus Reid   | 5500005555555559
-- Aiko Tanaka   | 378282246310005

-- Wrong: position 13 only works for 16-digit cards
SELECT customer_name, '**** ' || SUBSTRING(card_number, 13, 4) AS masked_card
FROM payments;

-- Output:
-- customer_name | masked_card
-- --------------+------------
-- Lena Fischer  | **** 1111
-- Marcus Reid   | **** 5559
-- Aiko Tanaka   | **** 005

-- Right: start 4 characters from the end
SELECT customer_name, '**** ' || SUBSTRING(card_number, LENGTH(card_number) - 3, 4) AS masked_card
FROM payments;

-- Output:
-- customer_name | masked_card
-- --------------+------------
-- Lena Fischer  | **** 1111
-- Marcus Reid   | **** 5559
-- Aiko Tanaka   | **** 0005

In MySQL and SQL Server, join the strings with CONCAT() instead of ||. SQL Server also needs LEN() instead of LENGTH().