Postgres Substring

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

This tutorial will explain how to use the Postgres substring function to retrieve a substring from a string. Sometimes a string in a database might contain information embedded in a substring. For example, the user may want to retrieve just a tracking or ID number contained in the string. This function is also helpful when splitting up a column into multiple substrings, provided those substrings have a use separate from the original string.

Prerequisites

  • PostgreSQL must be properly installed and configured on the local system to execute the Postgres substring function.

What is Postgres Substring

A substring will return part of the full string that matches a defined set of criteria within this function. The basic form of the substring function syntax is:

1
SUBSTRING ( <the_string> ,<starting_position> , <length_of_extraction> )

Here is a detailed explanation of the above syntax:

  • the_string means any string with a data type of varchar, text, char, etc.

  • starting_position is a type a integer that defines the starting point for extracting the substring. Note that the starting_position must have a positive value.

  • length_of_extraction defines the length or the number of characters that are being targeted for retrieval from the full string. The starting point is from the specified value in the starting_position.

Note that if the combined value of the length_of_extraction and starting_position exceeds the number of the characters within the string, the function will return the entire string from the starting_position. Additionally, while using the length_of_extraction is optional, if it is not defined the request will return the entire string from the specified starting_position.

Postgres Substring Function Example

The previous section provided an overview of the Postgres Substring Function. This section will provide examples that will demonstrate the function.

Substring Example One

The following substring example demonstrates a starting point and the length_of_extraction functions:

1
2
SELECT
   SUBSTRING ('Yogi Bear', 1, 6);

The output should resemble the following:

1
2
3
4
substring
-----------
 Yogi B
(1 row)

The above example defined the starting_point of ‘1’ and the length_of_extraction at ‘6’. Note that this example returns a result of Yogi B because each space is counted as one character.

Substring Example Two

In the following example the length of extraction is omitted:

1
2
SELECT
   SUBSTRING ('Yogi Bear', 6);

The output should resemble the following:

1
2
3
4
substring
-----------
 Bear
(1 row)

As previously mentioned, if the leng_of_extaction is omitted, Postgres will return the entire string starting from the specified starting_position.

Substring Example Three

The following example shows an alternative way of requesting the substring, but achieves similar results:

1
2
SELECT
   SUBSTRING ('Yogi Bear' FROM 1 FOR 8);

The output should look like:

1
2
3
4
 substring
-----------
 Yogi Bea
(1 row)

The above syntax uses words to specify the starting point, here using FROM and FOR for the number of characters being requested.

Substring Example with Regular Expression

This section will provide an example of the Postgres substring function using a regular expression with the three parameters used in the following code:

1
SUBSTRING(<the_string> FROM <the_pattern> FOR <escape-character>);

Following is a breakdown of the above example:

  • the_string is the standard string targeted for retrieval from the substring.
  • escape-character this is simply the escape character used to invoke an alternative interpretation on subsequent characters.

  • the_pattern is the regular expression pattern enclosed by the escape characters followed by a double quote. Note that the_pattern must match the substring, or a null value will be returned.

Here is an example:

1
2
3
4
5
SELECT SUBSTRING (
   'Yogi Bear'
   FROM
      '%#"Y__i#"%' FOR '#'
);

The results should resemble the following:

1
2
3
4
substring
-----------
 Yogi
(1 row)

Substring Function against Postgres data example

This section will explain how to use the Postgres substring function against Postgres records.

Following is the sample table used for this example:

1
2
3
4
5
6
7
8
9
  name  | age | gender
--------+-----+--------
 josh   |   4 | male
 jack   |   6 | male
 gaby   |   5 | male
 jacy   |   4 | female
 daniel |   6 | female
 jamey  |   5 | female
(6 rows)

Execute the following query:

1
2
3
4
5
6
7
8
SELECT
   name,
   age,
   SUBSTRING( name, 1, 1 ) AS initial
FROM
   tblsample
ORDER BY
   age;

Following is a breakdown of the above query:

  • The tblsample for the records was retrieved and then the name and the age columns were selected.
  • The initial was set by taking the first letter of the name.

The results of the above query should resemble the following:

1
2
3
4
5
6
7
8
9
  name  | age | initial
--------+-----+---------
 john   |   4 | j
 jane   |   4 | j
 greg   |   5 | g
 jessy  |   5 | j
 james  |   6 | j
 denise |   6 | d
(6 rows)

Conclusion

This tutorial explained how to use the Postgres substring function to retrieve a substring from a string. The tutorial specifically explained what a Postgres substring is and gave a detailed breakdown of the function’s syntax. The article also provided several substring function examples, including an example with a regular expression and an example against Postgres data. Remember that the regular expression pattern must match the substring when using a regular expression to target a substring or a null value will be returned.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.