In PostgreSQL, you can extract a substring from a column using the SUBSTRING() function. This function allows you to specify the starting position and the length of the substring you want to extract.
- Get substring from string in PostgreSQL using SUBSTRING() function.
- Get Substring of string in PostgreSQL using SUBSTRING() with FROM and FOR
- Extract First N characters using Substing() function
Syntax of SUBSTRING() Function in PostgreSQL:
SUBSTRING (string ,start_position , length )
- string: The string from which to extract the substring.
- start_position: The starting position of the substring (1-based index).
- length: The number of characters to extract.
Example 1: Basic Usage
To extract a substring from a specific string:
SELECT SUBSTRING('PostgreSQL',1,4) AS substring;
Output:
Example 2: Extracting Substring Based on Positions – SUBSTRING() with FROM and FOR
To extract a substring from a specific string based on position, we have used SUBSTRING() with FROM and FOR
SELECT SUBSTRING('PostgreSQL' FROM 1 FOR 4) AS substring;
Output:
Example 2b: Extracting Substring Based on Positions
To extract a substring from a specific string based on position we have used only FROM keyword , FROM defines the starting position and as there is no FOR it will take till the end.
SELECT SUBSTRING('PostgreSQL' FROM 5) AS substring;
Output:
Example 3: Extracting Substring from a Column
Suppose you have a table employees_email with a column email, and you want to extract the domain part of the email addresses:
To extract the domain part of the email addresses:
We use substring and position with FROM which defines the starting position and as there is no FOR it will take till the end. So it will start at position @ and takes till end.
SELECT *, SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain FROM employees_email;
Output:
Example 4: Get substring of the column in PostgreSQL
Substring of the column in PostgreSQL is extracted using SUBSTRING() function. To extract the first 7 characters:
select *, substring(email,1,7) as email_new from employees_email
So the resultant dataframe will be
Output:
