---
description: In this PostgreSQL substring tutorial, we will learn definitions, syntax, matching substrings with regular expression with examples.
title: PostgreSQL SUBSTRING() Function with Regex Example
image: https://www.guru99.com/images/postgresql-substring-function-with-regex.png
---

 

[Skip to content](#main) 

**⚡ Smart Summary**

PostgreSQL SUBSTRING extracts and returns part of a string, using an optional starting position and length, and it also matches text against a POSIX regular expression to pull out patterns such as numbers or codes.

* 📋 **Syntax:** substring( string \[from start\] \[for length\] ) returns a portion of the source string.
* 1️⃣ **Positions:** Character positions begin at 1, and omitting the length reads to the string end.
* 🔎 **Regex:** SUBSTRING(string FROM pattern) extracts the first text matching a POSIX regular expression.
* 🧮 **Grouping:** A parenthesized group in the pattern returns only that captured part of the match.
* 🖥️ **pgAdmin:** Every substring query runs the same way inside the pgAdmin query editor.
* 🤖 **AI Help:** AI assistants generate and explain regex patterns for accurate substring extraction.

[ Read More ](javascript:void%280%29;) 

![PostgreSQL SUBSTRING Function with Regex](https://www.guru99.com/images/postgresql-substring-function-with-regex.png)

## What is PostgreSQL Substring?

The PostgreSQL substring function helps you to extract and return part of a string. Instead of returning the whole string, it only returns a part of it, which is useful for trimming codes and reading fixed-width fields.

## Syntax

The PostgreSQL substring function takes the following syntax:

substring( string [from starting_position] [for length] )

## Parameters

The following parameters are used with the substring function:

| **string**             | The source string whose data type is varchar, char, string, etc.                                                                                                                                          |
| ---------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **starting\_position** | An optional parameter. It denotes the place where the extraction of the string will begin. If you omit this parameter, the extraction starts from position 1, which is the first character in the string. |
| **length**             | An optional parameter. It denotes the number of characters to be extracted from the string. If you omit this parameter, the function extracts from starting\_position to the end of the string.           |

## Examples

In this example, we want to extract the first 4 characters from the word Guru99:

SELECT substring('Guru99' for 4);

The command will return the following:

[](https://www.guru99.com/images/1/102219%5F1420%5FPostgreSQLS1.png)

We did not specify the starting position, so the extraction of the substring started at position 1\. Four characters were extracted to return the result above.

The following example shows how to specify the starting position:

SELECT substring('Guru99' from 1 for 4);

The command will return the following:

[](https://www.guru99.com/images/1/102219%5F1420%5FPostgreSQLS2.png)

We specified that the extraction of the substring should begin from position 1, and 4 characters should be extracted.

Let us extract 99 from the string Guru99:

SELECT substring('Guru99' from 5);

The command will return the following:

[](https://www.guru99.com/images/1/102219%5F1420%5FPostgreSQLS3.png)

We specified the starting position as 5\. Since the number of characters to be extracted was not specified, the extraction ran to the end of the string.

Here is another example:

SELECT substring('Guru99' from 5 for 2);

The command will return the following:

[](https://www.guru99.com/images/1/102219%5F1420%5FPostgreSQLS4.png)

We started extraction at position 5, and 2 characters were extracted.

Consider the Book table given below:

[](https://www.guru99.com/images/1/102219%5F1420%5FPostgreSQLS5.png)

We want to get a rough idea about the name of each book. We can extract only the first 15 characters from the name column of the table:

SELECT
   id,
   SUBSTRING(name, 1, 15 ) AS name_initial
FROM
   Book
ORDER BY
   id;

The command will return the following:

[](https://www.guru99.com/images/1/102219%5F1420%5FPostgreSQLS6.png)

We now have a rough idea about the name of every book.

### RELATED ARTICLES

* [PostgreSQL Tutorial for Beginners ](https://www.guru99.com/postgresql-tutorial.html "PostgreSQL Tutorial for Beginners")
* [PostgreSQL Union, Union ALL with Examples ](https://www.guru99.com/postgresql-union-example.html "PostgreSQL Union, Union ALL with Examples")
* [PostgreSQL EXISTS with SELECT Operator (Example) ](https://www.guru99.com/postgresql-exists.html "PostgreSQL EXISTS with SELECT Operator (Example)")
* [PostgreSQL INSERT: Inserting Data into a Table ](https://www.guru99.com/postgresql-insert.html "PostgreSQL INSERT: Inserting Data into a Table")

## Matching Substrings with SQL Regular Expression

In PostgreSQL, you can extract a substring that matches a specified POSIX regular expression. In this case, the substring function uses the following syntax:

SUBSTRING(string FROM matching_pattern)

or

SUBSTRING(string, matching_pattern);

Here is an explanation of the above parameters. The string is the source string, whose [data type](https://www.guru99.com/postgresql-data-types.html) is varchar, char, string, and so on. The matching\_pattern is the pattern used for searching in the string.

### Examples

SELECT
   SUBSTRING (
      'Your age is 22',
      '([0-9]{1,2})'
   ) as age;

The command will return the following:

[](https://www.guru99.com/images/1/102219%5F1420%5FPostgreSQLS7.png)

Our input string is Your age is 22\. In the pattern, we search for a numeric pattern; when it is found, the substring function extracts only two characters.

## How to Match Substrings Using pgAdmin

Now let us see how these actions are performed using pgAdmin. The queries that do not need a database can be executed directly from the query editor window. Just do the following:

**Step 1)** Login to your pgAdmin account.

**Step 2)** On pgAdmin, click the Query Tool icon.

[](https://www.guru99.com/images/1/102219%5F1420%5FPostgreSQLS8.png)

The query editor window will be opened.

**Step 3)** Type the following query in the editor window.

SELECT substring('Guru99' for 4);

**Step 4)** Click the Execute icon to execute the query.

[](https://www.guru99.com/images/1/102219%5F1420%5FPostgreSQLS9.png)

**Step 5)** Query execution is done. It should return the following:

[](https://www.guru99.com/images/1/102219%5F1420%5FPostgreSQLS10.png)

**Example 2:**

SELECT substring('Guru99' from 1 for 4);

It should return the following:

[](https://www.guru99.com/images/1/102219%5F1420%5FPostgreSQLS11.png)

**Here is the next example:**

SELECT substring('Guru99' from 5);

It should return the following:

[](https://www.guru99.com/images/1/102219%5F1420%5FPostgreSQLS12.png)

**Example 3:**

SELECT substring('Guru99' from 5 for 2);

It should return the following:

[](https://www.guru99.com/images/1/102219%5F1420%5FPostgreSQLS13.png)

Now, let us run the example using the Book table of the Demo database:

**Step 1)** Login to your pgAdmin account.

**Step 2)** From the navigation bar on the left, click Databases, then click Demo.

[](https://www.guru99.com/images/1/102219%5F1420%5FPostgreSQLS14.png)

**Step 3)** Type the query in the query editor:

SELECT
   id,
   SUBSTRING(name, 1, 15 ) AS name_initial
FROM
   Book
ORDER BY
   id;

**Step 4)** Click the Execute button.

[](https://www.guru99.com/images/1/102219%5F1420%5FPostgreSQLS15.png)

It should return the following:

[](https://www.guru99.com/images/1/102219%5F1420%5FPostgreSQLS16.png)

We now have a basic idea of the name of every book.

### Matching Substrings with SQL Regular Expression

To accomplish the same on pgAdmin, do the following:

**Step 1)** Login to your pgAdmin account.

**Step 2)** Click the Query Tool icon.

[](https://www.guru99.com/images/1/102219%5F1420%5FPostgreSQLS17.png)

The query editor window will be opened.

**Step 3)** Type the following query in the editor window.

SELECT
   SUBSTRING (
      'Your age is 22',
      '([0-9]{1,2})'
   ) as age;

**Step 4)** Click the Execute icon to execute the query.

[](https://www.guru99.com/images/1/102219%5F1420%5FPostgreSQLS18.png)

It should return the following:

[](https://www.guru99.com/images/1/102219%5F1420%5FPostgreSQLS19.png)

[ Download the Database used in this Tutorial](https://drive.google.com/uc?export=download&id=1BqQSvYTmNz4hvZok7iYG9ha%5Fo48%5FVF2b)

## FAQs

🔤 What is the difference between SUBSTRING and SUBSTR in PostgreSQL?

SUBSTR is a shorter alias of SUBSTRING and returns part of a string. SUBSTRING also supports the SQL-standard FROM and FOR keywords plus POSIX regular expressions, while SUBSTR only accepts comma-separated position and length arguments.

1️⃣ Does PostgreSQL SUBSTRING start counting at 0 or 1?

PostgreSQL SUBSTRING is one-based, so the first character sits at position 1, not 0\. Passing a starting position of 1 returns the string from its first character onward.

❓ What does SUBSTRING return when a regular expression finds no match?

When the POSIX pattern matches nothing, SUBSTRING returns NULL rather than an empty string. Testing the result with IS NULL lets you handle rows where the expected pattern is absent.

✂️ How do I extract text after a specific character, such as an email domain?

Use a regular expression that captures what follows the character. For example, SUBSTRING(email FROM ‘@(.\*)’) returns everything after the @ sign, giving you the domain part of each email address.

🔁 When should I use regexp\_matches() instead of SUBSTRING with a regex?

Use SUBSTRING when you need the first matching text as a simple value. Choose regexp\_matches() when you want every match or multiple capture groups returned as an array, since SUBSTRING returns only the first match.

🤖 How can AI help write PostgreSQL SUBSTRING regex patterns?

AI assistants such as GitHub Copilot turn a plain-English description into a POSIX pattern, explain what each token matches, and suggest test strings, which reduces trial-and-error when building substring extraction queries.

🧠 Can an AI assistant convert a plain-English rule into a SUBSTRING query?

Yes. Describe the part of the text you want, and the AI assistant drafts a SUBSTRING call with the correct positions or regular expression, which you can run and refine inside pgAdmin.

🔠 Is SUBSTRING regular expression matching case-sensitive?

Yes, POSIX SUBSTRING matching is case-sensitive by default. To match regardless of case, lower both inputs, for example SUBSTRING(lower(name) FROM lower(pattern)), so uppercase and lowercase letters are treated the same.

#### Summarize this post with:

ChatGPT Perplexity Grok Google AI 

**Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.** 

##### Sign up for the newsletter

Subscribe for Free 

You have successfully subscribed.  
Please check your inbox. 

![AI-Newsletter]() Chosen by over **350,000+** professionals 

[Scroll to top ](#wrapper)Scroll to top 

× 

Toggle Menu Close 

Search for: 

Search

```json
{"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/postgresql-substring-function-with-regex.png","url":"https://www.guru99.com/images/postgresql-substring-function-with-regex.png","width":"700","height":"250","caption":"PostgreSQL SUBSTRING() Function with Regex","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/postgresql-substring.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/postgresql","name":"PostgreSQL"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/postgresql-substring.html","name":"PostgreSQL SUBSTRING() Function with Regex Example"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/postgresql-substring.html#webpage","url":"https://www.guru99.com/postgresql-substring.html","name":"PostgreSQL SUBSTRING() Function with Regex Example","dateModified":"2026-07-02T12:06:14+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/postgresql-substring-function-with-regex.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/postgresql-substring.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/juniper","name":"Juniper Willow","description":"I am Juniper Willow, a PostgreSQL Developer, offering expert guidance to help you optimize and master PostgreSQL database development.","url":"https://www.guru99.com/author/juniper","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/juniper-willow-author.png","url":"https://www.guru99.com/images/juniper-willow-author.png","caption":"Juniper Willow","inLanguage":"en-US"},"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"image":{"@id":"https://www.guru99.com/images/postgresql-substring-function-with-regex.png"},"headline":"PostgreSQL SUBSTRING() Function with Regex Example","description":"In this PostgreSQL substring tutorial, we will learn definitions, syntax, matching substrings with regular expression with examples.","keywords":"postgresql, sql","@type":"Article","author":{"@id":"https://www.guru99.com/author/juniper","name":"Juniper Willow"},"dateModified":"2026-07-02T12:06:14+05:30","copyrightYear":"2026","name":"PostgreSQL SUBSTRING() Function with Regex Example","articleSection":"PostgreSQL","subjectOf":[{"@type":"HowTo","name":"How to matching substrings using pgAdmin","description":"Let's take a look at an example of how to matching substrings using pgAdmin","step":[{"@type":"HowToStep","name":"Step 1) Login your account.","text":"Login to your pgAdmin account.","url":"https://www.guru99.com/postgresql-substring.html#step1"},{"@type":"HowToStep","name":"Step 2) On pgAdmin,","text":"Click the Query Tool icon.","image":"https://www.guru99.com/images/1/102219_1420_PostgreSQLS8.png","url":"https://www.guru99.com/postgresql-substring.html#step2"},{"@type":"HowToStep","name":"Step 3) Type query.","text":"Type the query SELECT substring ('Guru99' for 4); on the editor window.","url":"https://www.guru99.com/postgresql-substring.html#step3"},{"@type":"HowToStep","name":"Step 4) Execute query.","text":"Click the Execute icon to execute the query.","image":"https://www.guru99.com/images/1/102219_1420_PostgreSQLS9.png","url":"https://www.guru99.com/postgresql-substring.html#step4"},{"@type":"HowToStep","name":"Step 5) Query execution is done.","text":"It should return the Guru.","image":"https://www.guru99.com/images/1/102219_1420_PostgreSQLS10.png","url":"https://www.guru99.com/postgresql-substring.html#step5"}]}],"@id":"https://www.guru99.com/postgresql-substring.html#schema-84488","isPartOf":{"@id":"https://www.guru99.com/postgresql-substring.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/postgresql-substring.html#webpage"}}]}
```