PostgreSQL substring returns part of a text value, by position or by pattern: substring('614-555-0142' FROM 1 FOR 3) returns 614.
The catch: a start of 0 isn’t bumped up to 1. substring('Hannah' FROM 0 FOR 3) gave me Ha, two characters.
The five examples cover area codes, email domains, regex captures and edge cases. Tested on PostgreSQL 18.6 for Windows.
PostgreSQL SUBSTRING Function Syntax
Here’s the syntax:
substring ( string text [ FROM start integer ] [ FOR count integer ] ) → text
substring ( string text FROM pattern text ) → text
substring ( string text SIMILAR pattern text ESCAPE escape text ) → text
substr ( string text, start integer [, count integer ] ) → text
| Argument | What it means |
|---|---|
start | First character kept, from 1 (default 1). |
count | Characters kept (default: the rest). Negative errors. |
pattern | POSIX (FROM) or SQL (SIMILAR) regex. |
Return value: text. It’s NULL if any argument is NULL or nothing matches.
Availability: SIMILAR … ESCAPE needs PostgreSQL 14 or later (older: FROM pattern FOR escape). For varchar input, see text vs varchar in PostgreSQL.
When to Use the SUBSTRING Function
Use substring when you need to:
- pull area codes from phone numbers
- trim ZIP+4 codes to five digits
- split an email at the @
- grab text matching a pattern
Length-based cuts pair well with the PostgreSQL LENGTH function. Run this setup once; you’ll reuse it everywhere:
CREATE TABLE customers (
id int PRIMARY KEY,
full_name varchar(20),
phone varchar(12),
zip varchar(10),
email varchar(30),
city_line varchar(25)
);
INSERT INTO customers VALUES
(1, 'Hannah Brooks', '614-555-0142', '43215-1904', 'hannah.brooks@gmail.com', 'Columbus, OH 43215'),
(2, 'Tyler Reed', '512-555-0187', '78701', 'treed@outlook.com', 'Austin, TX 78701'),
(3, 'Grace Miller', '303-555-0119', '80202-3301', 'grace@millerdesign.co', 'Denver, CO 80202'),
(5, 'Olivia Carter', NULL, '98101-2210', 'olivia.carter@icloud.com', 'Seattle, WA 98101');
Example 1: Extract Area Codes and ZIP Codes With SUBSTRING
Let’s start simple. I want the area code and the five-digit ZIP:
SELECT full_name,
substring(phone FROM 1 FOR 3) AS area_code,
substring(zip FROM 1 FOR 5) AS zip5
FROM customers
WHERE id IN (1, 5);
Output:
full_name | area_code | zip5
---------------+-----------+-------
Hannah Brooks | 614 | 43215
Olivia Carter | NULL | 98101

For Hannah Brooks this returns 614 and 43215. Olivia’s NULL phone gives NULL.
Example 2: Use SUBSTR to Get the Last Four Digits
Now substr, the comma version. Say you need the last four digits:
SELECT full_name,
substr(phone, 9) AS last4,
substring(phone, 5, 3) AS prefix
FROM customers
WHERE id <= 2;
Output:
full_name | last4 | prefix
---------------+-------+--------
Hannah Brooks | 0142 | 555
Tyler Reed | 0187 | 555
You get 0142 and 555 for Hannah. With no count, substr doesn’t stop until the end, and substring(phone, 5, 3) shows the comma form of substring.
Example 3: Extract an Email Domain With SUBSTRING and POSITION
Emails need a moving cut point. The goal is the domain and the first name:
SELECT email,
substring(email FROM position('@' IN email) + 1) AS domain,
substr(full_name, 1, strpos(full_name, ' ') - 1) AS first_name
FROM customers
WHERE id IN (1, 3);
Output:
email | domain | first_name
-------------------------+-----------------+------------
hannah.brooks@gmail.com | gmail.com | Hannah
grace@millerdesign.co | millerdesign.co | Grace

For grace@millerdesign.co you’ll get millerdesign.co and Grace.
position() finds the @ at 6, so the cut starts at 7. To join pieces back, I have a separate guide on PostgreSQL CONCAT.
Example 4: Extract a ZIP Code With SUBSTRING and Regex
Next, a pattern. I want the ZIP out of city_line, plus a SIMILAR domain:
SELECT city_line,
substring(city_line FROM '[A-Z]{2} \d{5}') AS no_group,
substring(city_line FROM '[A-Z]{2} (\d{5})') AS zip_group,
substring(email SIMILAR '%@#"%#"' ESCAPE '#') AS domain
FROM customers
WHERE id IN (2, 5);
Output:
city_line | no_group | zip_group | domain
-------------------+----------+-----------+-------------
Austin, TX 78701 | TX 78701 | 78701 | outlook.com
Seattle, WA 98101 | WA 98101 | 98101 | icloud.com

For Austin, the plain pattern returns TX 78701; the one with (\d{5}) returns 78701.
With parentheses, you don’t get the whole match; you get the first group. In SIMILAR, #" marks the part to return. To swap matched text, use PostgreSQL regexp_replace.
Example 5: SUBSTRING With Start 0 or a Negative Length
Last, odd inputs that’ll surprise you. I want to see what each returns:
SELECT substring('Hannah' FROM 0 FOR 3) AS from_0,
substring('Hannah' FROM -2 FOR 5) AS from_neg2,
substring('Hannah' FROM 4 FOR 50) AS past_end,
substring('Hannah' FROM 10) AS start_past;
SELECT substr('Hannah', 2, -1);
Output:
from_0 | from_neg2 | past_end | start_past
--------+-----------+----------+------------
Ha | Ha | nah |
psql:substring-start-zero-negative-count.sql:5: ERROR: negative substring length not allowed

FROM 0 FOR 3 and FROM -2 FOR 5 both return Ha, because the window starts where you said and only the characters that really exist inside it survive.
A start past the end gives an empty string, not NULL. Run it again and you should see a blank start_past.
Tips & Common Mistakes
- Negative counts don’t clamp, they fail:
ERROR: negative substring length not allowed. Wrap them ingreatest(n, 0). - No space means
strposreturns 0 and the first name comes back empty. substring(phone FROM 1 FOR 3) = '614'skips an index on phone. PostgreSQL LIKE'614-%'can use atext_pattern_opsindex.
Wrapping Up: Which SUBSTRING Form Should You Use?
Copy Example 1 for fixed spots, Example 3 when the cut point moves and Example 4 when a pattern describes the text better than any position could. substr is the comma-only shorthand; substring is the SQL-standard name.
Moving a query over? Compare the SQL Server SUBSTRING function.
Next step: browse the complete list of PostgreSQL functions or the PostgreSQL 18 string functions docs.
Other PostgreSQL tutorials you may also like:
Frequently Asked Questions
How do I get the substring before a character in PostgreSQL?
split_part(email, '@', 1). Negative fields count from the end (PostgreSQL 14+).
Does PostgreSQL substring work on bytea?
Yes. On bytea, start and count are bytes, not characters.
Is the PostgreSQL substring regex case-sensitive?
Yes. Start the pattern with (?i) to ignore case.

Kumar Saurabh is a freelance software engineer who builds web and mobile apps with Next.js, React, Flutter and Python. From 2021 to 2024, Saurabh worked as an IT analyst and developer at TSInfo Technologies, writing technical articles on SQL and PostgreSQL. Saurabh writes the PostgreSQL, MySQL, MariaDB and MongoDB tutorials on DatabaseFAQs.com and holds a Master of Computer Applications (MCA) from Chandigarh University.