PostgreSQL SUBSTRING() Function with Regex Example

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:

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:

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:

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:

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:

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

Consider the Book table given below:

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:

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

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 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:

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.

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.

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

Example 2:

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

It should return the following:

Here is the next example:

SELECT substring('Guru99' from 5);

It should return the following:

Example 3:

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

It should return the following:

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.

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.

It should return the following:

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.

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.

It should return the following:

Download the Database used in this Tutorial

FAQs