PostgreSQL – Substring Function

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: