PostgreSQL SUBSTRING Function

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
ArgumentWhat it means
startFirst character kept, from 1 (default 1).
countCharacters kept (default: the rest). Negative errors.
patternPOSIX (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
PostgreSQL substring FROM 1 FOR 3 returning area code 614 and ZIP 43215 in VS Code
Area code and ZIP5.

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
PostgreSQL substring with position returning email domain millerdesign.co and first name Grace
Domain after the @.

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
PostgreSQL substring FROM regex with a capture group returning ZIP 78701 instead of TX 78701
The group wins.

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
PostgreSQL substring FROM 0 FOR 3 returning Ha and substr with negative length raising an error
Start 0, negative count.

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 in greatest(n, 0).
  • No space means strpos returns 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 a text_pattern_ops index.

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.