Skip to content
Related Articles

Related Articles

Improve Article

PostgreSQL – Substring Function

  • Last Updated : 01 Feb, 2021

In PostgreSQL, the substring function is used to extract a substring from a string.

Syntax: SUBSTRING ( string, start_position, length )

Let’s analyze the above syntax:

  • The string is a string whose data type is char, varchar, text, etc.
  • The start_position is used to specify the part from where the substring is to be returned. It is normally an integer value.
  • The length argument is used to determine till where the substring is to extracted. It holds an integer value that determines the length of the substring.

Example 1:

Here we query data from the customer table. Here we operate on the last_name and first_name column. We  will extract the initial name by querying the first character of the first_name column from the customer table of the sample database:

SELECT
    last_name,
    SUBSTRING( first_name, 1, 1 ) AS initial
FROM
    customer
ORDER BY
    last_name;

Output:



Example 2:

The following statement extracts the house number (maximum 4 digits, from 0 to 9) from a string:

SELECT
    SUBSTRING (
        'The house no. is 9001',
        '([0-9]{1, 4})'
    ) as house_no;

Output:

My Personal Notes arrow_drop_up
Recommended Articles
Page :