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: