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:
Share your thoughts in the comments
Please Login to comment...